Default Selection for Latest Period (Data for Latest Period)

[pradeept]

Hi Community,

Need your help.

Sceanrio: I have 2 slicers in one page. Year (YYYY) and Month (YYYYMM). 

1) When I open the the dashboard, the slicer has to  set for the latest Year and latest Month. i.e the data in all charts should be filtred for this. 

2) If I select any Year, then it should filter for the latest/max Month in that Year, and all charts should filtered for this. 

3) If I select any Month, then it should show the related month data only across the charts.

Sample Data: below are the 2 slicers.

Year: 2021, 2020, 2019

Months: 202103, 202102, 202101, 202012, 202011,...,202001,201912,201911,...,201901

Expectation: 

1) by default the charts should showcase 202103 data (it should be dynamic)

2) if I select 2020, it should showcase data for the month of 202012.

3) if I select 201910, it should showcase data for the month of 201910

I googled for this, but couldn't get it.  Appreciate your help. Thank in advance.

Regards

Pradeep

[v-yangliu-msft]

Hi  @pradeept ,

Here are the steps you can follow：

1. Create a table of two slicers

Slicer1 = SUMMARIZE('Table',[Year])

Slicer2 = SUMMARIZE('Table',[Month])

2. Create measure.

Flag =
var _selectyear=HASONEVALUE('Slicer1'[Year])
var _selectmonth=HASONEVALUE('Slicer2'[Month])
return
SWITCH(
    TRUE(), 
_selectyear=FALSE()&&_selectmonth=FALSE(),IF(MONTH(MAX('Table'[date]))=4&&YEAR(MAX('Table'[date]))=2021,1,0),
_selectyear=FALSE(),IF(MAX('Table'[Month])=SELECTEDVALUE(Slicer2[Month]),1,0),
_selectmonth=FALSE(),IF(SELECTEDVALUE(Slicer1[Year])=2020,IF(MAX('Table'[Month])=202012,1,0),IF(MAX('Table'[Month])=20214,1,0)),
IF(MAX('Table'[Month])=SELECTEDVALUE(Slicer2[Month]),1,0)
)

3. Place the measure in the Filter, select is =1, Apply filter.

4. Result.

When 2021 is selected or not, the following is displayed:

When 2020 is selected, the result shows:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pradeept · Accepted solution]

@v-yangliu-msft thanks for your help.

I don't want to use the static dates;  the dates should be dynamic,  in measures calculation. 

I tried to overcome the filtering at each visual as well.

Finally got some solution, after tweaking my initial DAX measure. Here is the DAX measure calculation I used to achieve this.. 

SalesAmount Latest Period=

Var _MaxReportPeriod =

CALCULATE(

MAX(Sales[ReportingPeriod]),

ALLEXCEPT(Sales,Sales[ReportingPeriod],Sales[ReportingYear])

)

RETURN

CALCULATE(

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

)

[pradeept]

@amitchandak 

@v-yangliu-msft 

Thanks for your responses.

just an update to my requirement, no need to make the default selection in the slicer.
If the charts/tables reflects the 'Max reporting period, that's sufficient.

I used the below DAX  for measure calculation.

SalesAmount =
VAR _MaxReportPeriod = MAX(Sales[ReportingPeriod])

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

I am getting the 'max reporting period' and 'measure' as expected and tested in Table/KPI cards; results are as expected.

Problem:
When I used the measure along with any other dimensions (in my example customer), getting the wrong results. 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 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.

Similar to 'Customer', there are couple of other dimensions.. (Country,Product, Age Group..). So looking for generic solution.

Thanks in advance.

[v-yangliu-msft]

Hi  @pradeept  ,

According to your description, I create some data:

Here are the steps you can follow:

1. Create calculated Table.

Slicer = SUMMARIZE('Table',[Year])

2. Create measure.

Flag =
var _selectyear=HASONEVALUE('Slicer'[Year])
var _selectmonth=HASONEVALUE('Table'[Month])
return
SWITCH(
    TRUE(),  _selectyear=FALSE()&&_selectmonth=FALSE(),CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),MONTH('Table'[date])=3&&YEAR('Table'[date])=2021)),
_selectyear=FALSE(),SUM('Table'[amount]),
_selectmonth=FALSE(),CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),MONTH('Table'[date])=12&&YEAR('Table'[date])=SELECTEDVALUE(Slicer[Year]))),
    BLANK())

3. Place the measure in the Filter, select is =1, Apply filter.

4. Result.

By default, the data for the month of 2021.3 are displayed:

Select 2020, you should display the data for 202012:

Select 201910, it will display the data for October 2019:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@pradeept , You have to create columns like these on you date Table

Month Type = Switch( True(),
eomonth([Date],0)= eomonth(Today(),0),"Current Month" ,

eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
Format([Date],"MMM-YYYY")
)

Year Type = Switch( True(),
year([Date])= year(Today()),"This Year" ,
Format([Date],"YYYY")
)

Select This Year and current month

refer my video

https://www.youtube.com/watch?v=hfn05preQYA

[pradeept]

@amitchandak  thanks for you suggestion. but this is not what I am looking for exactly.

Step-1:

When we open the page/dashboard, by default Year/Month should be filtered automatically  for "This Year" and "This Month" . There should not be any manual process for selections. (labels should be '2021', '202103')

Step-2:

After analysing the charts/reports for latest period, if any further analysis required then we will select the necessary time periods. 

Thanks, pradeep