Display Current and Previous Months

[djcau]

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

[v-frfei-msft · Accepted solution]

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