Forum Discussion

RT6's avatar
RT6
Frequent Visitor
2 years ago
Solved

same months comparison over the years - DAX

Hi all!

 

i use this DAX 

CY = CALCULATE(SUM('Market'[Sales]), DATESMTD('Calendar'[Date]))/1000;
 
and for the last year 
LY = CALCULATE(SUM('Market'[Sales]),DATESMTD(dateadd('Calendar'[Date],-1,YEAR)))/1000
 
the issue is when i select filter for example jan&feb 2024, it shows only value for February 2024 & 2023 accordingly. But i need a sum for of all selected months  
 
Any suggestions?
DAX
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi RT6 

     

    Thanks for the reply from Dangar332 , please allow me to provide another insight:

     

    My sample:

     

     

    Create a measure as follows

    comparison = 
VAR _previous = CALCULATE(SUM(Market[Sales]), FILTER(Market, [Date] IN SAMEPERIODLASTYEAR('Calendar'[Date])))
VAR _current = CALCULATE(SUM(Market[Sales]), FILTER(ALL(Market), [Date] IN VALUES('Calendar'[Date])))
RETURN
DIVIDE(_previous + _current, 1000)

     

    Result:

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Hi, RT6 

     you have slicer in month form and you want sum of month wise then use DATESYTD()

     

    use DATESYTD instead of DATESMTD

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi RT6 

     

    Thanks for the reply from Dangar332 , please allow me to provide another insight:

     

    My sample:

     

     

    Create a measure as follows

    comparison = 
VAR _previous = CALCULATE(SUM(Market[Sales]), FILTER(Market, [Date] IN SAMEPERIODLASTYEAR('Calendar'[Date])))
VAR _current = CALCULATE(SUM(Market[Sales]), FILTER(ALL(Market), [Date] IN VALUES('Calendar'[Date])))
RETURN
DIVIDE(_previous + _current, 1000)

     

    Result:

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • RT6's avatar
      RT6
      Frequent Visitor
      2 years ago

      hi Yulia, 

       

      thanks for your response! SAMEPERIODLASTYEAR worked well in this case 🙂