Need your help in solving the below scenario.
Sceanrio: I have 2 slicers in one page. 'Reporting Year'(YYYY) and 'Reporting Period' (YYYYMM).
1) When I open the dashboard, by deafult the data in all charts should be filtred to the latest Reporting Period.
2) If I select any Reporting Year, then all charts should be filtered for the latest/max ReportingPeriod in the selected Year.
3) If I select any Reporting Period, then it should show the related month data only across the charts.
I used the below DAX for measure calculation.
Var _MaxReportPeriod = Max(Sales[ReportingPeriod])
SUM(Sales[Amount]),Sales[ReportingPeriod] = _MaxReportPeriod
Tested the above DAX calculation in Table/KPI cards; getting the 'max reporting period' and 'measure' as expected.
When I used the measure along with any other dimensions (in my example customer), getting the wrong results. It showing all the customers, even they did not purchase anything in that period.
1) When I select 2021 or did not select anything...
The max reporting period is 202104. During 202104, the customers C1,C2,C3 only did the purchase. So the Chart/table should show C1,C2,C3 only; should not show C4,C5,C6.
2) When I select year 2020
The max reporting period is 202012. During 202012, the customers C1,C2,C4,C6 only did the purchase. So the Chart/table should show C1,C2,C4,C6 only; should not show C3,C5.
3) If I select the periods 202104 or 202012, the numbers and the customers are showing corectly across the charts. (working fine)
Note: Similar to 'Customer', there are couple of other dimensions.. (Country,Product, Age Group..). So looking for generic solution.
Thanks in Advance
Solved! Go to Solution.
Hi @pradeept ,
Try to do like this.
1, Create a new calculated table.
Table = DISTINCT(Sales[ReportingPeriod])
2, Add the field of the new table to the slicer.
3, Create the measure like this.
SalesAmount = Var _MaxReportPeriod = ALLSELECTED(Table[ReportingPeriod]) Retrun CALCULATE ( SUM(Sales[Amount]),Sales[ReportingPeriod] in _MaxReportPeriod )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-lionel-msft thanks for response.
If I select the reporting period (lowest granularity in my time filters), I am getting the correct results with your approach and my approach. If I select the 'year only' or 'year not selected', i am getting the extra dimensions/X-axis values in the charts and table. Please have a look at my first post.
Thanks in advance.
@pradeept , Try to create a column like this in you date table and filter on that
Dafault Month year =
if('Date'[Month year] = Max(Sales[ReportingPeriod]) , "Max" , [Month year] & "")
Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=35
@amitchandak thanks for quick response.
With this approach, the user has to select the the 'Max' or any period after openinng the dashboard.
In real scenario, the metric was YTD numbers. So the data should be auto filtered in all the charts for the latest period upon opening, otherwise in first glance it provides the wrong results.
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps
Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.