Need help creating a revenue variance dashboard using LOD\Fixed calculation

[baksh3s]

Hi, 

New to power BI and building a revenue variance dashboard with data containing 4 fields, 
Customer Code, Product Code, Period ,Revenue

Calculations are :
New Revenue = Current Period Revenue > 0 and Last Period Revenue = 0 then Current Period Revenue, else 0
Lost Revenue = Last Period Revenue > 0 and current period revenue = 0 then - Last period revenue, else 0 
revenue increase = if  (AND(current revenue > last period revenue, last period revenue>0) , current revenue - last period revenue, 0)
similar for revenue decrease.

Above measures are to be calculated at customer-product combination and should give the same aggregated result when viewed by even at just product or just customer level, but this is not working properly.

For example, in screenshot below: The product table (top left) shows Lost Revenue as 0 for all products, but it should be -120900 for ZJQ70 and -145656 for ZJM08, similar issues with other calculations. help appreciated 

[baksh3s]

thanks a lot, this worked. 🙌

It would have been better if i was allowed to attach the pbix file

[pankajnamekar25]

You can upload the pbix to Google drive and share that link here that will work

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[pankajnamekar25 · Accepted solution]

Hello @baksh3s 

try these measure

New Revenue =

SUMX (

    ADDCOLUMNS (

        SUMMARIZE (SalesData, [Customer Code], [Product Code]),

        "CurrentRev", CALCULATE (SUM(SalesData[Revenue]), SalesData[Period] = "2023 Q2"),

        "LastRev", CALCULATE (SUM(SalesData[Revenue]), SalesData[Period] = "2023 Q1")

    ),

    IF ( [LastRev] = 0 && [CurrentRev] > 0, [CurrentRev], 0 )

)

Lost Revenue =

SUMX (

    ADDCOLUMNS (

        SUMMARIZE (SalesData, [Customer Code], [Product Code]),

        "CurrentRev", CALCULATE (SUM(SalesData[Revenue]), SalesData[Period] = "2023 Q2"),

        "LastRev", CALCULATE (SUM(SalesData[Revenue]), SalesData[Period] = "2023 Q1")

    ),

    IF ( [LastRev] > 0 && [CurrentRev] = 0, [LastRev], 0 )

)

Revenue Increase =

SUMX (

    ADDCOLUMNS (

        SUMMARIZE (SalesData, [Customer Code], [Product Code]),

        "CurrentRev", CALCULATE (SUM(SalesData[Revenue]), SalesData[Period] = "2023 Q2"),

        "LastRev", CALCULATE (SUM(SalesData[Revenue]), SalesData[Period] = "2023 Q1")

    ),

    IF ( [CurrentRev] > [LastRev] && [LastRev] > 0, [CurrentRev] - [LastRev], 0 )

)

Revenue Decrease =

SUMX (

    ADDCOLUMNS (

        SUMMARIZE (SalesData, [Customer Code], [Product Code]),

        "CurrentRev", CALCULATE (SUM(SalesData[Revenue]), SalesData[Period] = "2023 Q2"),

        "LastRev", CALCULATE (SUM(SalesData[Revenue]), SalesData[Period] = "2023 Q1")

    ),

    IF ( [LastRev] > [CurrentRev] && [CurrentRev] > 0, [LastRev] - [CurrentRev], 0 )

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.