Forum Discussion

SniperPro's avatar
SniperPro
Frequent Visitor
3 years ago

Calculating YTD while keeping Month filter

I have bellow table data:      I want to add a measure calculating the YTD of Value so I added this measure : Mesure = CALCULATE(SUM('Table'[Value]), DATESYTD('Calendar'[Date])) Then in my...
"DAX"
DATESYTD Help needed
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

hi SniperPro 

try like:

Mesure = 
CALCULATE(
     SUM('Table'[Value]),      DATESYTD('Calendar'[Date]),
     ALL('Table'[Product])
)
 
it seems you want to ignore the Product filter, but why adding it in the visual?
SniperPro's avatar
SniperPro
Frequent Visitor
3 years ago

hello FreemanZ 
I want to add a month filter on the report page level :

Then I want to see the product on this month (Product2) and the YTD Value on this month alose (3) 
Below the result that I want to see :

 

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi SniperPro 

    try like:

     

    Measure = 
CALCULATE(
     SUM(TableName[Value]),      
     YEAR(TableName[Date])=YEAR(MAX(TableName[Date])),
     TableName[Date]<=MAX(TableName[Date])
)

     

     

    it worked like:

     

    • SniperPro's avatar
      SniperPro
      Frequent Visitor
      3 years ago

      FreemanZ Is it possible to have this same formula using the date from the related calendar table and not from the TableName.

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi SniperPro 

    it is always adivisable to have a dimensional date table. Supposing you have one, then try like:

    Measure2 = 
CALCULATE(
     SUM(TableName[Value]),      
     DATESYTD(DateTable[Date])
)

    or 

    Measure3 = 
TOTALYTD(
    SUM(TableName[Value]),
    DateTable[Date]
)

    it worked like: