Forum Discussion

ramk's avatar
ramk
Regular Visitor
8 years ago
Solved

YTD as dynamic value using DAX

HI,   I am looking to make the YTD measure to be dynamic based on a input parameter. This input parameter then will be used a slicer in power pivot.   For ex: To calculate YTD, we use : IsYT...
Need Help
  • Salonic's avatar
    Salonic
    8 years ago

    Hello,

     

    By using a WhatIf parameter, you may let the users choose their parameters dynamically.

     

    I created a simple table :

     

    I then created a WhatIf parameter :

    LastDays = GENERATESERIES(0; 50; 1)

     

    Inside the data table, I added 3 new measures :

    Date Min = DATE(YEAR(TODAY()-LastDays[Last DAys Val]);1;1)
    Date Max = TODAY()-LastDays[Last DAys Val]
    Sales YTD = 
     SUMX(
     FILTER(Data;AND(Data[Date]<[Date Max];Data[Date]>= [Date Min]));
     Data[Units])

    When users change the value of LastDays on a slicer, it will dynamically change the scope :

     

     

     

Salonic's avatar
Salonic
Resolver I
8 years ago

Hello,

 

By using a WhatIf parameter, you may let the users choose their parameters dynamically.

 

I created a simple table :

 

I then created a WhatIf parameter :

LastDays = GENERATESERIES(0; 50; 1)

 

Inside the data table, I added 3 new measures :

Date Min = DATE(YEAR(TODAY()-LastDays[Last DAys Val]);1;1)
Date Max = TODAY()-LastDays[Last DAys Val]
Sales YTD = 
 SUMX(
 FILTER(Data;AND(Data[Date]<[Date Max];Data[Date]>= [Date Min]));
 Data[Units])

When users change the value of LastDays on a slicer, it will dynamically change the scope :