How to filter DAX same period last year column

Anonymous — Wed, 27 May 2020 07:55:21 GMT

Hello,

I have this table with units and units of the previous period. To be able to filter with this slicer i added to the SPLY metric an ALL() function, because if i do not, the metric doesn't show values when i filter by one specific month as in the screencapture.

My problem now is that i need to show in the table only the values of the month-year filtered in the slicer and the total of only this specific period. Is there any dax function to combine with ALL() in order to display only the selected datetime.

The way i calculate Same period last year column is:

Units SPLY = CALCULATE (

SUM ( 'Tabla'[Units Actual] );

ALL(Tabla[Date]);

SAMEPERIODLASTYEAR (Tabla[Date].[Date])

)

Thank you!

Re: How to filter DAX same period last year column

vivran22 — Mon, 01 Jun 2020 00:42:31 GMT

Hello Anonymous ,

Ideally, the slicers will slice visuals and show only relevant dates. It is not clear from your example if you have created a calendar table in your data model. For time intelligence function to work properly, it is recommended to have a calendar table in the data model.

I have recreated a sample scenario, and used following measures:

Total Orders (SPLY) = CALCULATE( [Total Orders], SAMEPERIODLASTYEAR(ftCalendar[Date]) ) Total Orders A(SPLY) = CALCULATE( [Total Orders], ALL(ftCalendar[Date]), SAMEPERIODLASTYEAR(ftCalendar[Date]) )

Following is the result:

As you can see, both the formula is giving me the desired results and the visual is filtering accordingly.

In this, I have created a calendar table and used it in the model.

You may refer to the article for adding a calendar table:

https://www.vivran.in/post/power-bi-time-intelligence-calendar-table

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Feel free to email me for any BI needs.

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

topic Re: How to filter DAX same period last year column in DAX Commands and Tips

How to filter DAX same period last year column

Re: How to filter DAX same period last year column