Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to create relative date filter using dax

Hi everyone,

 

I am trying to create a measure to filter my graph based on relative dates. but my measure is not working unfortunately

My default graph looks like 

 

I'd like that if i click Previous Months button, it should display all past data until end of last month (September 30 2020).

IF current month is Nov 2020, i should see all past data until end of last month (oct 31 2020) and so on. 

 

and if i click the Current Month -> button, it should only display values from current month (from Oct 1 2020) until the max date with value. if current month = December, i should see all data from dec 1 2020 until the max date with value.

 

i have a calendar table (Date) that is linked to my transactions table (Client Ddate). what i did is i created a calculated column inside Calendar table:  

TodayDate =
var today = FORMAT(TODAY(), "mm/YYYY")
return
DATEDIFF(today,'Calendar'[Date],month)

 

then created measures to filter the past dates and current + future dates

Past Date =CALCULATE('Transaction'[A.Value], FILTER('Calendar', 'Calendar'[TodayDate] < 0))
 
CurrentFuture =CALCULATE('Transaction'[A.Value], FILTER('Calendar', 'Calendar'[TodayDate] = 0 || 'Calendar'[TodayDate] > 0))
 
PROBLEM: the measures are not filtering the graph when used in switch formula bec i think my measures might be incorrect in the first place 
 

I hope you can help me with this. Thank you so much in advance!

 

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , refer if this old solution can be bit of help

    https://community.powerbi.com/t5/Desktop/Required-custom-date-Slicer-Last-7-days-last-15-days-last-30/m-p/1284966#M561629

     

    For this month vs last month use time intelligence with date table

    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 month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
    this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
    last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
    previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))

    diff = [MTD Sales]-[last MTD Sales]
    diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

     

    Power BI — Qtr on Qtr with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839

     

    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 :radacad sqlbi My Video Series Appreciate your Kudos.

     

    Please provide your feedback comments and advice for new videos
    Tutorial Series Dax Vs SQL Direct Query PBI Tips
    Appreciate your Kudos.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi amitchandak really sorry i totally forgot to respond to you. Anyway, thank you so much for your solution. it worked perfectly. I appreciate it very very much. 

  • hsantosh's avatar
    hsantosh
    Helper II
    2 years ago

    i am using relative date slicer
    same i want to display in other card visual start date and end date from that slicer