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 wou...
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)))

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
    )
)