Find totals for previous month based on selectedValue

[Rt123]

I am trying to calculate totals for previous month (dont have date dimension table) with month names. By using the below formula, there is no error but its giving me blank value. e.g. if selected month = Feb (monthNum = 8), retreive Jan(monthNum = 8-1) totals.

Total Actuals_PM = SUMX(FILTER(L_Cube, (L_Cube[[Scenario]]] = "Actual") &&  (L_Cube[[Financial Year]]] = "2024") && L_Cube[MonthNum] = (SELECTEDVALUE(L_Cube[MonthNum])-1)), L_Cube[[Amount]]])

Much appreciated..

[v-kongfanf-msft · Accepted solution]

Hi @Rt123 ,

The best way to do this entails creating creating a date table that is connected to your data table through a one-to-many relationship.

Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 01, 01 ), DATE ( 2024, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Quarter", ROUNDUP ( MONTH ( [Date] ) / 3, 0 ),
    "Month", MONTH([Date]),
    "Week", WEEKNUM ( [Date] ),
    "Year-Qur",
        YEAR ( [Date] ) & "Q"
            & ROUNDUP ( MONTH ( [Date] ) / 3, 0 ),
    "Year-Mon",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "Year-Week",
        YEAR ( [Date] ) * 100
            + WEEKNUM ( [Date], 2 ),
    "Weekday", WEEKDAY ( [Date], 2 )
)

Then with the following formula, you can use the SELECTEDVALUE function to dynamically get the last month's value.

result_ =
VAR sel_mon =
    SELECTEDVALUE ( 'Date'[Month] )
VAR max_ =
    CALCULATE ( MIN ( 'Date'[Date] ), FILTER ( 'Date', 'Date'[Month] = sel_mon ) ) - 1
VAR min_ =
    CALCULATE ( MIN ( 'Date'[Date] ), 'Date'[Month] = MONTH ( max_ ) )
RETURN
    CALCULATE ( SUM ( 'Table'[Value] ), DATESBETWEEN ( 'Date'[Date], min_, max_ ) )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

@v-kongfanf-msft there are several issues with the proposed calendar table.  Note that there is a QUARTER() function.  Note that WEEKNUM requires specification of the second parameter to be deterministic, same for Year-Week.

The last couple columns should all be generated via the FORMAT command, not via math.

[lbendlin]

you will want to invest in a calendar table.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...