Default latest period issue in the charts/reports

[pradeept]

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.

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

[pradeept · Accepted solution]

@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

)

[v-lionel-msft]

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.

[pradeept]

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

@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

[pradeept]

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