Show last month of filtered period

[Josef]

Hi Guys, 

I have a question and hope you guys can help me.

I created a monthly report template in Power BI Desktop with multiple pages.

In the template I applied a date slicer where I select the YEAR and MONTH.

Both slicers are synced to all pages from the report, so all data in the report will be automatically updated to the selected year and month from the slicers. 

Now are there some pages in the report where I would like to show:

1. A table with figues of the selected month from the slicer (in this example only February data)
2. A column chart with the year to date figures per month (in this example January AND February data)

When I both sync the YEAR and MONTH slicer to these pages, the column chart is not correct.

When I only sync the YEAR slicer, my table is not correct. 

Do you guys know if this is possible without applying a seperate filter on the table? For example with some DAX code to only show the last month of the slected period in the slicer.

My goal is to only change the YEAR and MONTH slicer, to update all pages of the report, without having to change extra filters on some pages. 

Many thanks in advance!

[v-yiruan-msft]

Hi @Josef ,

Whether the problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help other members in the community find the solution easily if they face the similar problem with you. Thank you.

Best Regards

Rena

[Josef]

@v-yiruan-msft I haven't figured out yet how to get the right figures (I get small differences all the time between the formulas and munual filtering of the same period). There must be something I overlook... At he beginning of next week I will be able to perform more extensive tests and then I will post here is the problem is solved. Aplogies for the delay. 

[v-yiruan-msft]

Hi @Josef ,

Thanks for your feedback. If you need any help from my side about this thread, please feel free to contact me. 

Best Regards

Rena

[Anonymous]

I just posted this for another thread that was looking for something simular. Hope it helps! FOrrest

Prior_Month = CALCULATE(SUM(Table2[Price]), // Sum Price
DATESBETWEEN(Table2[Salesdate], // Based on Dates Between
Date(YEAR(EDATE(today(),-1)),MONTH(EDATE(today(),-1)),1), // Date having Year = Today -1 Month / Month = today -1 Month, and Day = 1
EOMONTH(today(),-1))) // End Of Month effective today() -1 month

Admin

apps4rent | o365cloudexperts | clouddesktoponline

[amitchandak]

@Josef 

If we use Time intelligence with date calendar we will be able to show last month with current month selected.

Example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))


last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))


MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/