Max value for available month, quarter, and year.

[jwin2424]

Hello. I have tried a few different formulas, and cannot seem to get anything to work the way I want it to. 

I have two tables. I have a Calendar table, and I have an Data table. They are connected together by the date (day) on a one-to-many relationship (with the many side being the Inventory table). 

I have a matrix visualization as shown below, and I am using this formula. Please note I do not use time-intelligence, as I am using a fiscal calendar. 

Final Number =

CALCULATE(

    SUM(Data[Net]),

    'Calendar'[Month]=MAX('Calendar'[Month])

)

The formula works in 2022, where the year total is the ending value for the year and the quarter totals are the ending value of each quarter, but for Q3 of 2023, it wont provide me a value NOR will it provide me a value for 2023. The correct value for both 2023 and Q3 should be the last month number with a value. In this case, my latest month is month 7, so Q3 should be month 7 and 2023 should be month 7. When I have data for month 8, I want Q3 to show month 8 and 2023 to show month 8. 

I am not sure what to do. 

Thansk!

[tamerj1]

@jwin2424 
Please try

Final Number =
VAR LastMonth =
    CALCULATE ( MAX ( 'Calendar'[Month] ), KEEPFILTERS ( Data ) )
RETURN
    CALCULATE ( SUM ( Data[Net] ), 'Calendar'[Month] = LastMonth )

[Martin_D · Accepted solution]

Hi @jwin2424 ,

This code solves your problem. In line 2 you need to check whether the naming of the date (day) columns matches with your data model and adjust the column names if needed:

Final Number = 
VAR _DatesWithNet = TREATAS ( VALUES ( 'Data'[day] ), Calendar[day] )

VAR _LastMonthWithNet = 
    CALCULATE (
        MAX ( 'Calendar'[Month] ),
        _DatesWithNet
    )

VAR _NetInLastMonth =
    CALCULATE (
        SUM ( 'Data'[Net] ),
        'Calendar'[Month] = _LastMonthWithNet
    )
RETURN
_NetInLastMonth

BR

Martin

[jwin2424]

Thank you Martin. This is a long way of solving it, as I realized from this that I was asking for the max date in the calendar table and not the max date in the data table. The reason it wasnt populating was because there was no data in month 12 for 2023. Your above formula does just that, but I simplified it to this

Final Number =

CALCULATE(

    SUM(Data[Net]),

    'Calendar'[Month]=MAX('Data'[Month])

)

Though I am not sure if there is any flaw in doing it the above way vs. yours for various visualizations. 

[Martin_D]

@jwin2424  Your solution works fine. Your original post just didn't indicate that there is a Data[Month] column in your data model 😉