cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pradeept
Resolver I
Resolver I

Default latest period issue in the charts/reports

Hi Community,

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.

SalesAmount =
Var _MaxReportPeriod = Max(Sales[ReportingPeriod])
Retrun
CALCULATE
(
SUM(Sales[Amount]),Sales[ReportingPeriod] = _MaxReportPeriod
)

 

Tested the above DAX calculation in Table/KPI cards;  getting the 'max reporting period' and 'measure' as expected.

 

Problem:
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.


Expected:
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.

 

Max Reporting Period_2021.png
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.

 

Max Reporting Period_2020.png

 

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

1 ACCEPTED SOLUTION
pradeept
Resolver I
Resolver I

@v-lionel-msft @amitchandak thanks for your help.
Finally got some solution, after tweaking my initial DAX measure. Here is the DAX I used to achieve this.. 


SalesAmount =
Var _MaxReportPeriod =
CALCULATE(
MAX(Sales[ReportingPeriod]),
ALLEXCEPT(Sales,Sales[ReportingPeriod],Sales[ReportingYear])
)

RETURN
CALCULATE(
SUM(Sales[Amount]), Sales[ReportingPeriod] = _MaxReportPeriod
)

 

View solution in original post

5 REPLIES 5
pradeept
Resolver I
Resolver I

@v-lionel-msft @amitchandak thanks for your help.
Finally got some solution, after tweaking my initial DAX measure. Here is the DAX I used to achieve this.. 


SalesAmount =
Var _MaxReportPeriod =
CALCULATE(
MAX(Sales[ReportingPeriod]),
ALLEXCEPT(Sales,Sales[ReportingPeriod],Sales[ReportingYear])
)

RETURN
CALCULATE(
SUM(Sales[Amount]), Sales[ReportingPeriod] = _MaxReportPeriod
)

 

View solution in original post

v-lionel-msft
Community Support
Community Support

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
)

 

Best regards,
Lionel Chen

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.  

amitchandak
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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.

 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors