Forum Discussion
Display Current and Previous Months
Hi Guys,
Would just like to look for some help in dashboard.
I am trying to create a chart/report wherein whenever i select a specific yearmonth in a slicer, I will still be able to see the previous months in the chart/report within the fiscal year.
Example:
If I select "December 2018" in the slicer,
My table/chart should still show and their equivalent results.
July
August
September
October
November
December
Thank you
Hi djcau,
Here I made an sample based on you description. You can refer to the following steps to meet your requirement.
1. Enter a list of date and create some calculated columns using the formulas as below.
yyyymm = FORMAT('date'[date],"mmmm yyyy")Year = YEAR('date'[date])2. Create a date table and create a calcualed column based on the date table.
Table = CALENDARAUTO()
YM = FORMAT('Table'[Date],"mmmm yyyy")3. New a measure using the formula as below.
Measure = IF(MAX('date'[date])<=MAXX(ALLSELECTED('Table'),'Table'[Date]) && MAX('date'[Year])=MAXX(ALLSELECTED('Table'),YEAR('Table'[Date])),1,0)Then we can get the result as we excepted. Here we should use the YM column as a slicer.
Please notice here the relationship between date table and fact table is inactive, for more details, please check the pbix as attached.
https://www.dropbox.com/s/wihx4i5bwuihkdd/Display%20Current%20and%20Previous%20Months.pbix?dl=0
Regards,
Frank
2 Replies
v-frfei-msftCommunity Support
Hi djcau,
Here I made an sample based on you description. You can refer to the following steps to meet your requirement.
1. Enter a list of date and create some calculated columns using the formulas as below.
yyyymm = FORMAT('date'[date],"mmmm yyyy")Year = YEAR('date'[date])2. Create a date table and create a calcualed column based on the date table.
Table = CALENDARAUTO()
YM = FORMAT('Table'[Date],"mmmm yyyy")3. New a measure using the formula as below.
Measure = IF(MAX('date'[date])<=MAXX(ALLSELECTED('Table'),'Table'[Date]) && MAX('date'[Year])=MAXX(ALLSELECTED('Table'),YEAR('Table'[Date])),1,0)Then we can get the result as we excepted. Here we should use the YM column as a slicer.
Please notice here the relationship between date table and fact table is inactive, for more details, please check the pbix as attached.
https://www.dropbox.com/s/wihx4i5bwuihkdd/Display%20Current%20and%20Previous%20Months.pbix?dl=0
Regards,
Frank
- v-frfei-msft
Hi djcau,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank