Forum Discussion

spagad6263's avatar
spagad6263
Icon for Post Patron rankPost Patron
10 months ago
Solved

How can I calculate table total without first month?

Hi,

 

I have a table showing last 13 months sales from Sep 2024 to Sep 2025. I would like to set the first month color as RED, which is already done with the help from Power BI community. Now I would like to have the total as sum of the last 12 months, instead of 13. Can someone suggest and help? I use below DAX but it doesn't work

Last 12 Months Total =
VAR MinDate = MIN('Calendar'[Date])
VAR StartDate = DATE(YEAR(MinDate), MONTH(MinDate) + 1, 1)
VAR EndDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
    [_Sales],
    DATESBETWEEN(
        'Calendar'[Date],
        StartDate,
        EndDate
    ),
    ALLSELECTED('Calendar')
)




Need Help
  • Ahmedx's avatar
    Ahmedx
    10 months ago

    pls try this
    VAR _MaxDate = EOMONTH( MAX('Calendar'[Date]),0)
    RETURN
    CALCULATE( [_Sales],
    KEEPFILTERS(DATESINPERIOD('Calendar'[Date],_MaxDate,-12,MONTH)))

5 Replies

    • PBI_Consultant's avatar
      PBI_Consultant
      Frequent Visitor
      10 months ago

      You can create and update an app in the Power BI service. You can also upload a Power BI report file to the workspace and include it in the app. However, you cannot directly upload a Power BI app file itself. Could you please clarify what exactly you're trying to do?
      Thanks

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    10 months ago

    pls try this
    VAR _MaxDate = EOMONTH( MAX('Calendar'[Date]),0)
    RETURN
    CALCULATE( [_Sales],
    KEEPFILTERS(DATESINPERIOD('Calendar'[Date],_MaxDate,-12,MONTH)))

    • spagad6263's avatar
      spagad6263
      Icon for Post Patron rankPost Patron
      10 months ago

      Thanks for the quick reply. I think your advice should work. I use below to get the answer I need. What I am trying to get now is to show the monthly sales as they are, but shows the total of last 12 month. Here is the screeshot. Instead of showing 23103, I would like to have the total as 21269. Any suggestion?

       



      Last 12 Months Total =
      VAR MinDate = MIN('Calendar'[Date])
      VAR StartDate = DATE(YEAR(MinDate), MONTH(MinDate) + 1, 1)
      VAR EndDate = MAX('Calendar'[Date])
      RETURN
      CALCULATE(
          [_Sales],
          FILTER(
              ALL('Calendar'[Date]),
              'Calendar'[Date] >= StartDate &&
              'Calendar'[Date] <= EndDate
          )
      )