• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

DAX issue with PowerPivot based Dashboard

Ian MacLeod

New Member
Hello.
I wonder if someone can help me. I'm struggling with this one.
I have a Dashboard (having studied Chandoo's Dashboard (Excel School 2.0 Dashboards) course.
I have initially used Power Query to ETL the Source data and Loaded it all to a Data Model.
Then I am using the Data Model to create Pivot Tables (as per Chandoo's course)
Everything works perfectly except for one thing.
On the Dashboard I am using a Timeline to filter on dates.
The DB looks allows the user to view the YTD Actuals against the FY (Financial Year 12 months) Total Forecast to see the value and % of the Year's Forecast has already been spent.
The idea is for the user to select various periods, say month 1 - 10 or 4 -6 or a single month in the year.

How can I have the Timeline filter on selected periods? But always keep the FY Forecast as the 12 months to:
1. always show the Full Year Amount,
2. calculate remining spend value and %,
3. calculate the % spent at that Timeline selected.

The Timeline uses a separate table in the Data Model.

I've tried to use a DAX expression in the Pivot Table in question by using CALCULATE, but I can't seem to be able to use REMOVEFILTERS, that is, from the Timeline field.

I'm running out of time for this to be done for my employer.
Please can someone help me?
I'm afraid I am unable to attached any files, as my employer has a need for tight security.
Please ask me direct questions, if my description of my issue is not clear enough to help resolve.

Thank you in advance

Ian MacLeod
 
I assume you have Calendar table that has all dates of given year.

1. You use CALCULATE(Measure,FILTER(ALL(Table),Year([DateColumn])="2024")) to obtain data from all of calendar date based on condition. You can make "2024" variable in Dax. Alternately you'd use ALLEXCEPT()
2. Simple subtraction will give remaining budget. % can be calculated using DIVIDE()
3. Same as 2. DIVIDE()

Have a read of below to understand difference between ALL, ALLEXCEPT
 
Back
Top