Forum Discussion

DanCasSan's avatar
DanCasSan
Helper V
6 years ago

Conditional measure with Timeline filter

Hello community, how are you?

 

I need your help with this. I have three columns with different quantities, one of them shows the total information per year, another one per month and another one per day. I also have a Timeline filter, and when the year is filtered, show the year quantities, when I open the granulate and select a month, it shows me the quantities of the month field and when it reaches the day it shows me the quantities of the day field. Can this be done on a conditional measure? Where you integrate the interaction with these three columns in a single measure.

 

I appreciate your support.

Interesting Links
Need Help
Tips and Tricks
Tutorial Requests

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    @DanCasSan , Try as with date calendar

    Day  Sales =
var _max = maxx(allselected('Date'),'Date'[Date])
return
 CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]=_max))

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

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((Table[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR(Table[Date])),"12/31"))

    To get the best out of the time intelligence feature. Make sure that you have a date calendar and it has been marked as the date in the model view. Also, join him with the date column of his/her fact/s. See:
    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/

  • VijayP's avatar
    VijayP
    Community Champion
    6 years ago

    DanCasSan 

    Is it possible to just snap sample of your source data as I am not able visualise how Year/Month/Date related information in different columns

    • DanCasSan's avatar
      DanCasSan
      Helper V
      6 years ago

      Hello VijayP , thanks for your immediate response, so I currently have my details:

       

      In the column "AmountYear" the total of the year of the date is shown, in the column "AmountMonth" is the total of the month of the date and finally we have the column "AmountDay" where the amounts per day are shown. Now, what I want to do is that when I select in the TIMELINE filter, the year 2019, it should show me the amount of 33,302 and if I filter the month of May 2019 it should show me the amount 4,014.

       

      Thank you for your support!

       

      Regards,