Forum Discussion

cham's avatar
cham
Icon for Post Patron rankPost Patron
5 years ago
Solved

Previous Month to Date

Hi I have a issue with Matrix table sub total,   I have craeted below measures Amount Sales = SUM(Sales[Amount]) Prev Month To Date = IF([MTH]=1, 0, CALCULATE ([Amount Sales], DATEADD(Calender...
Need Help
Show and Tell
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    cham Try:

    Measure Total =
  VAR __Total = SUMX(SUMMARIZE('Table',[End of Month],"__PrevMonth",[PreviousMonthMeasure]),[__PrevMonth])
RETURN
  IF(HASONEVALUE('Table'[End of Month]),[PreviousMonthMeasure],__Total)
cham's avatar
cham
Icon for Post Patron rankPost Patron
5 years ago

Hi Greg_Deckler 

 

Please see the screesnhot below,

2020 year total is giving e the corrcet sum of 2020 months

 

But the measures I used to get the previous month value is correct. Every month is giving the previous month value but the Total of all the months values showing in sub total is incorrect.

 

WhatI can do for that.

 

thanks.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
5 years ago

Hi,

I am confused but I'd also like to help you.  Please show clearly in an MS Excel file, the result you are expecting.

  • cham's avatar
    cham
    Icon for Post Patron rankPost Patron
    5 years ago

    Hi Ashish_Mathur 

     

    Please find the below details,

    This is the Power BI table - which is not givingthe current SUB total of 2019 months

    This is the excel data set, Power BI sub total needs to be as below highlited total. 

     

    Thanks,

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      5 years ago

      cham Try:

      Measure Total =
  VAR __Total = SUMX(SUMMARIZE('Table',[End of Month],"__PrevMonth",[PreviousMonthMeasure]),[__PrevMonth])
RETURN
  IF(HASONEVALUE('Table'[End of Month]),[PreviousMonthMeasure],__Total)
      • cham's avatar
        cham
        Icon for Post Patron rankPost Patron
        5 years ago

        hi Greg_Deckler ,

         

        Its not giving the correct totals what I want. Thank measure change my month totals as well.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      5 years ago

      Hi,

      Share the link from where i can download your PBI file.