Calcualte actual and forecast with dynamic measure selection

[Marsfield2019]

Hi

I would like to build a report which is able to select the different versions from below table.

I duplicated the version into new table with heading "Reporting" and Comparative “with version.

Then I write the below measure to calculate the Current month and prior month.

Current Month = TOTALMTD(SUM([amount], Date[Period],FilterS[Reporting]

Prior Month = TOTALMTD(SUM([amount], Date[Period],FilterS[Comparative]

Budget Month will be different version.

I would expect the Current Month amount will be $550 if I select the reporting version = ‘Working’  with period “Sep” in the filter.

The Prior month will be $120 if I select reporting version = ‘Jul 2019 version’ with period “Sep”

Then I can work on the Var = Current month – Last Month

My question is how can build the dynamic  DAX?

Thanks in advance

[v-lid-msft · Accepted solution]

Hi @Marsfield2019 ,

How about the result after you follow the suggestions mentioned in my original post?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft]

Hi @Marsfield2019 ,

We use the following measure to archive your requirement:

Current Month = SUM('Dates'[Amount])

Prior Month = 
SUMX (
    FILTER (
        ALL ( 'Dates' ),
        Dates[Version] = SELECTEDVALUE ( Dates[Version] )
            && Dates[Date-Year-Month]
                >= SELECTEDVALUE ( Dates[Date-Year-Month] ) - 31
            && Dates[Date-Year-Month] < SELECTEDVALUE ( Dates[Date-Year-Month] )
    ),
    [Amount]
)

But based on the fact that you have a date column in your table, we could create a calculated date column using following formula:

MonthNumber = SWITCH([Period],"Jul",7,"AUG",8,"Sep",9) 
-- Add more based on your short name

Date-Year-Month = DATE([YEAR],[MonthNumber],1)

But if you already have one, you can skip these steps

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Marsfield2019]

Hi

I am sorry for late reply as I was busy on Month end reporting.

How about the comparsion?

I would like to know the var of September  between  "Working version" and "Jul version?

Thanks in advance

[v-lid-msft]

Hi @Marsfield2019 ,

Firstly, Create two calculate table contain all the version value and put them into two slicers:

VersionSelect-1 = DISTINCT('Dates'[Version])

VersionSelect-2 = DISTINCT('Dates'[Version])

Then we can create two measures to show the value based on the slicers

SelectedMonthVersion1 =
SUMX (
    FILTER (
        ALL ( Dates ),
        AND (
            'Dates'[Version] IN FILTERS ( 'VersionSelect-1'[Version] ),
            'Dates'[Period] IN FILTERS ( 'Dates'[Period] )
        )
    ),
    [Amount]
)

SelectedMonthVersion2 =
SUMX (
    FILTER (
        ALL ( Dates ),
        AND (
            'Dates'[Version] IN FILTERS ( 'VersionSelect-2'[Version] ),
            'Dates'[Period] IN FILTERS ( 'Dates'[Period] )
        )
    ),
    [Amount]
)

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.