Calculate difference between budget versions instead of date

[Anonymous]

Hi there, I'm trying to figure out a DAX measure to calculate differences between versions of forecast.  Each month we create a new forecast.  I would like to create a waterfall chart that bridges the changes between each version of the budget.

• I have a Data table (below) and Date Table.   
• I have a relationship between these two tables with the common "date" field. 
• I also have a slicer to toggle between apple and mango (ie. my dummy "customers"). 
• First DAX measure  - Current Version = sum(Data[Actuals])
• Second DAX measure - Previous Version = CALCULATE([Current Version],PREVIOUSMONTH('Date Table'[Date]))
• Third DAX measure - Change MoM = [Current Version] - [Previous Version]

When I create a waterfall with Month (from Date Table) on the X axis, it works.  However, when I put "Version" from Data table on the X axis the numbers come out wonky.    I cannot figure why!  I was hoping the relationship to the date table would fix it....

Version	Date of Version	Customer	Budget
Version 1	1-Jan-20	apple	1
Version 1	1-Jan-20	mango	5
Version 2	1-Feb-20	apple	2
Version 2	1-Feb-20	mango	3
Version 3	15-Mar-20	apple	6
Version 3	15-Mar-20	mango	10
Version 4	6-Apr-20	apple	12
Version 4	6-Apr-20	mango	14
Version 5	25-May-20	apple	15
Version 5	25-May-20	mango	22
Version 6	15-Jul-20	apple	30
Version 6	15-Jul-20	mango	15

I'ved saved a copy of the PBI file - https://drive.google.com/file/d/1XCYkurpJKpTSHbx9uy1_4trANOM7Mg38/view?usp=sharing 

[Anonymous]

Hello @MFelix and other users of the community

I was thinking of doing a new post, but will follow up here first.

The naming convention of the versions have since gotten more complicated

2018 actuals, 2019 actuals, 2020 actuals, 2021 1+11 forecast, 2021 2+10 forecast, 2021 3+9 forecast, etc.

I was wondering how can I tell Power BI that the "previous version" of 2019 actuals is 2018 actuals, and the previous version of 2021 3+9 forecast is 2021 2+10 forecast.   See attached file here

 https://drive.google.com/file/d/1kY5ge8YevRCDAwbkhsUkZPkAYDFTSKw3/view?usp=sharing  

Thank you!

[MFelix · Accepted solution]

Hi @Anonymous ,

Sorry for the late response, but I have done the following:

• Add the following column:

VersionID =
IF (
    SEARCH ( "+", Data[Version],, 0 ) = 0,
    LEFT ( Data[Version], 4 ) & "00",
    LEFT ( Data[Version], 4 )
        & SUBSTITUTE (
            RIGHT (
                LEFT ( Data[Version], SEARCH ( "+", Data[Version],, 0 ) - 1 ),
                SEARCH ( " ", Data[Version],, 0 ) - 3
            ),
            " ",
            "0"
        )
)

This was done with DAX but believe that the best option is to make it in the query editor so that you can Sort the version vby this ID:

if Text.Contains ([Version], "+")  then  
Text.Start([Version], 4) &
Text.PadStart(
Text.End(Text.Start([Version],Text.PositionOf([Version], "+")) ,

Text.PositionOf([Version], "+") - 
Text.PositionOf([Version], " ") -1
 ), 2, "0")

else Text.Start([Version], 4) & "00"

Now add the following code:

Previous Version by version _ = 
VAR currentVersion =
    SELECTEDVALUE ( Data[VersionID] )
VAR PreviousVersion =
    IF(RIGHT(currentVersion, 2 )  = "00", currentVersion - 100, currentVersion - 1)
VAR temp_table =
    FILTER (
        SUMMARIZE ( ALLSELECTED ( Data ), Data[VersionID], "Actuals", [Current Version] ),
        Data[VersionID] = PreviousVersion
    )
RETURN
    SUMX ( temp_table, [Actuals] )

Has you can see below and in attach PBIX the Calculation are matching but be aware that the split for the 2021 budget is not correct because of the dates leap since budget and actuals for january are in the same date.

In this file I have created a hierarchy to give corrects values in X-Axis but as I refere if you create the column in the query editor then you can sort the Version column by the versionId and no need for the hierarchy.

[Anonymous]

Hi @MFelix 

https://drive.google.com/file/d/1XCYkurpJKpTSHbx9uy1_4trANOM7Mg38/view?usp=sharing 

Can you try this one?  I uploaded my file to my google drive and changed the setting so anyone with the link can access.  If ther eis a better way to upload a file in this forum, let me know!

[MFelix]

Hi @Anonymous ,

The information you are getting is based on context what this means is that since your Previous versions is based on the date it will return correct when you have the date in your context when you change the context (remove the date) it will get incorrect.

If you had a measure that picks up the version code, I'm assuming that each month has the name Version 1, version 2, and so on:

Add the following measure:

Previous Version by version =
VAR currentVersion =
    SELECTEDVALUE ( Data[Version] )
VAR Find_Version =
    SEARCH ( " ", currentVersion,, LEN ( currentVersion ) )
VAR VersionNumber =
    RIGHT ( currentVersion, LEN ( currentVersion ) - Find_Version )
VAR temp_table =
    FILTER (
        SUMMARIZE ( ALLSELECTED ( Data ), Data[Version], "Actuals", [Current Version] ),
        Data[Version] = "Version " & VersionNumber - 1
    )
RETURN
    SUMX ( temp_table, [Actuals] )



Change MoM by version = [Current Version] - [Previous Version by version]

Result below and in attach file:

Be aware that since your measure is based on previous month and the Version 5 to version 6 goes from May to July the last 2 column by date are summarized on the last column by version.

PBIX file attach.

[MFelix]

Hi @Anonymous 

The file is asking for a password can you please share it without password.