Calculating Total Value Based on Last Day of the Year or Latest Data Available

[LadyCat]

Hi, 

I need help with the following: I have data that looks like this - 

Date of Purchase    Product       Value

Dec 31, 2022           A

Jan 31, 2023            A

Feb 28, 2023           A

Dec 31, 2022           B

Jan 31, 2023            B

Dec 31, 2022           C

Dec 31, 2022           D

Dec 31, 2022           E

Jan 31, 2023            E

Feb 28, 2023           E

I need to create a report (with a measure?) that will return two things:

- The total value for products A, B, C, D, E on Dec 31, 2022

- The latest total available value for all products, so for the above that would be the following -

Feb 28, 2023           A 

Jan 31, 2023            B

Dec 31, 2022           C

Dec 31, 2022           D

Feb 28, 2023          E

Is there any way to do this without writing complicated DAX? I am fairly new to Power BI.

Thank you! 

[Ashish_Mathur]

Hi,

Ceate a Calendar Table with a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  To your visual, drag Product from the Data Table.  Write these measures

Total = sum(Data[Value])

Total as on Dec 31 = calculate([Total],datesbetween(calendar[Date],date(2022,12,31),date(2022,12,31)))

Last date = max(Data[Date of Purchase])

Hope this helps.

[v-shex-msft · Accepted solution]

Hi @LadyCat,

#1, You can try to use the following measure formula to summary values based on specific date.

Total for the last day of previous year=
VAR _lastdate =
    MAXX ( ALLSELECTED ( table ), [Date] )
RETURN
    CALCULATE (
        SUM ( table[value] ),
        FILTER (
            ALLSELECTED ( table ),
            [Date]
                = DATE ( YEAR ( _lastdate ) - 1, 12, 31 )
        )
    )

#2, I think you can use measure filter to filter visual records.

For detail operation steps, you can write a measure formula to get the last date based on category and compare with current date to return flag. Then you can use it on visual level filter to filter records.

flag=
VAR currdate =
    MAX ( table[Date] )
VAR _lastDate =
    CALCULATE (
        MAX ( table[Date] ),
        ALLSELECTED ( table ),
        VALUES ( table[Product] )
    )
RETURN
    IF ( currdate = _lastDate, "Y", "N" )

Regards,

Xiaoxin Sheng