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

Default Selection for Latest Period (Data for Latest Period)

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

1 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
)

View solution in original post

6 REPLIES 6
v-yangliu-msft
Community Support
Community Support

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.

v-yangliu-msft_0-1620349968417.png

4. Result.

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

v-yangliu-msft_1-1620349968448.png

When 2020 is selected, the result shows:

v-yangliu-msft_2-1620349968456.png

 

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.

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

View solution in original post

pradeept
Resolver I
Resolver I

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

 

Max Reporting Period_2021.png

 

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.

 

Max Reporting Period_2020.png

 

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

 

Thanks in advance.

v-yangliu-msft
Community Support
Community Support

Hi  @pradeept  ,

According to your description, I create some data:

v-yangliu-msft_0-1619485614789.png

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.

v-yangliu-msft_1-1619485614795.png

4. Result.

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

v-yangliu-msft_2-1619485614796.png

v-yangliu-msft_3-1619485614797.jpeg

Select 2020, you should display the data for 202012:

v-yangliu-msft_4-1619485614798.png

v-yangliu-msft_5-1619485614799.jpeg

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

v-yangliu-msft_6-1619485614800.png

v-yangliu-msft_7-1619485614802.png

 

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
Super User IV
Super User IV

@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



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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors