Forum Discussion

Sathishkumar96's avatar
Sathishkumar96
New Member
4 years ago

Time intelligence slicer

Hi Team,

         I am new to power Bi. I want to create a Slicer with Time periods Say MTD, YTD, PYTD and show the Values in a card viual. I have created the Slicer but the problem is that YTD and PYTD are working Properly but for MTD i need to create a seperate month slicer and if i click a month say APRIL on that month slicer MTD shows correct values but my YTD is showing value for the selected April month and PYTD are showing values for the selected April month of the previous year. Kindly help me with it.

 

MTD = calculate(Sum(sheet1[sales]))
 
YTD = var a = FIRSTNONBLANK('Date'[Date],SUM(Sheet1[Sales]))
var b = LASTNONBLANK('Date'[Date],SUM(Sheet1[Sales]))
var c = CALCULATE(SUM(Sheet1[Sales]),'Date'[Date]>=a,'Date'[Date]<=b,All('Date'[month]))
return c
 
PYTD = var a = edate(FIRSTNONBLANK('Date'[Date],SUM(Sheet1[Sales])),-12)
var b = edate(LASTNONBLANK('Date'[Date],SUM(Sheet1[Sales])),-12)
var c = CALCULATE(SUM(Sheet1[Sales]),'Date'[Date]>=a,'Date'[Date]<=b,All('Date'[month]))
return c
 
Without Clicking the extra Month slicer my YTD and PYTD varels are correct but my MTD is wrong as it shows YTD values 
 

When Clicking the Extra Month Slicer 

 

 

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

1 Reply

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    I would encourage you to change how you are creating date filters in your DAX expressions, and leverage the time intelligence functions to simplify them. Here is a good article - Basics of Time Intelligence in DAX for Power BI; Year to Date, Quarter to Date, Month to Date - RADACAD

     

    You already have a Date table, which is good. Once it is "Marked as a date table", you can use the following expressions:

     

    MTD = calculate(Sum(sheet1[sales]), DATESMTD('Date'[Date]))

     

    YTD = calculate(Sum(sheet1[sales]), DATESYTD('Date'[Date]))

     

    PYTD = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DATESYTD('Date'[Date])))
     
    Pat