Forum Discussion

AshP's avatar
AshP
Frequent Visitor
2 years ago

Measure not working when selected multiple months

Hi, I created a below measure to calculate the previous month sales. It is working fine when I am selecting single month but not adding up when selecting multiple months. Eg. When selecting January, it is giving the right number but when I am selecting january and feb it is showing the latest month data but I need the sum. How can I fix this ?
 
Prevs_month =
 var curmon = Month(Max(calendar[date]))
 var curyer = year(max(calendar[date]))
 var prevmon = if( curmon = 1, 12, curmon-1)
 var preyer = if(curmon = 1,curyer-1,curyer)
 RETURN
  calculate(sum(table[sales]),
    Month('Calendar'[Date]) = prevmon,Year ('Calendar'[Date])= preyer)
Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    AshP , Try to use time intelligence functions

     

    examples

     

    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]))
    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]))
    next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
    this month = 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))))

     

    Power BI — Month on Month with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
    https://www.youtube.com/watch?v=6LUBbvcxtKA

    • AshP's avatar
      AshP
      Frequent Visitor
      2 years ago

      The same problem with time intelligence functions, They are working fine for individual months. But not working when selected more than 1 month. 

    • WILLIAMSKEY3's avatar
      WILLIAMSKEY3
      Frequent Visitor
      1 year ago

      Has there been any solution for this? I am running into the same issue with totals, when my slicer has multiple options chosen.