Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Remove filter off specific Column on Matrix

Hi everyone, I have a three column Matrix which is filtered by month. For one of them as supposed to having it calculate further by broken month I want to keep those values as YTD. How can I do this ...
Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    Just wanted to provide a solution to my own post! 

     

    There is a built in Total YTD Function that I used. Works like a charm for anyone trying to do something similar

    TOTALYTD(SUM(Column),DimDate[Year Month],ALL(DimDate),"10/31")
amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

for Ytd you can use time intelligence with date table

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))


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

 

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.
Appreciate your Kudos.