Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

URGENT: Variance Report

I need help figuring out this calculation in Power BI. It's easy in Tableau with date parameters, but not sure how to do it in Power BI.   The calculation rules are below:   Customer Start and S...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Anonymous ,

     

    The question here is regarding the period selected in the Current Period and the granularity.

     

    What I did as to make changes to your two measures:

    Previous Avg Price =
CALCULATE (
    SUMX (
        SUMMARIZE (
            'Invoice Variance',
            'Invoice Variance'[item],
            "CurAvgPrice",
                DIVIDE (
                    SUM ( 'Invoice Variance'[ext_price] ),
                    SUM ( 'Invoice Variance'[volume] ),
                    0
                )
        ),
        [CurAvgPrice]
    ),
    FILTER (
        ALL ( 'Invoice Variance'[inv_date] ),
        'Invoice Variance'[inv_date]
            <= MAX ( 'Current'[Date] ) - 365
            && 'Invoice Variance'[inv_date]
                >= MIN ( 'Current'[Date] ) - 365
    )
)



Current Avg Price =
CALCULATE (
    SUMX (
        SUMMARIZE (
            'Invoice Variance',
            'Invoice Variance'[item],
            "CurAvgPrice",
                DIVIDE (
                    SUM ( 'Invoice Variance'[ext_price] ),
                    SUM ( 'Invoice Variance'[volume] ),
                    0
                )
        ),
        [CurAvgPrice]
    ),
    FILTER (
        ALL ( 'Invoice Variance'[inv_date] ),
        'Invoice Variance'[inv_date] <= MAX ( 'Current'[Date] )
            && 'Invoice Variance'[inv_date] >= MIN ( 'Current'[Date] )
    )
)

     

    Has you can see below the lines that you refer are picking up the values:

     

    All others have only a single value previous or current. This is impacted by a slicer with the dates of current with 2021 dates.

     

    What is happening is that since you were using the full date table when you compare the current date 2020 with previous year 2019 you are not getting values because your data starts in 2020, on the other and for the current values when you search for the current value on 2021 you are not getting values because your data is of 2020.. (not sure if I made it clear)

     

    Check PBIX file attach.

     

     

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

The calculation is correct but I need the date included because they are trying to get a bar graph with 4 other variables (that customer start & stop thing mentioned before). So in this ecample, while that 5.57 is correct, the other rows should be 0 since they didnt have a matching value. This is where I was trying to use Count Rows maybe but I couldnt figure out how to do it correctly:

 

 

MFelix's avatar
MFelix
Super User
5 years ago

Hi Anonymous ,

 

The question here is regarding the period selected in the Current Period and the granularity.

 

What I did as to make changes to your two measures:

Previous Avg Price =
CALCULATE (
    SUMX (
        SUMMARIZE (
            'Invoice Variance',
            'Invoice Variance'[item],
            "CurAvgPrice",
                DIVIDE (
                    SUM ( 'Invoice Variance'[ext_price] ),
                    SUM ( 'Invoice Variance'[volume] ),
                    0
                )
        ),
        [CurAvgPrice]
    ),
    FILTER (
        ALL ( 'Invoice Variance'[inv_date] ),
        'Invoice Variance'[inv_date]
            <= MAX ( 'Current'[Date] ) - 365
            && 'Invoice Variance'[inv_date]
                >= MIN ( 'Current'[Date] ) - 365
    )
)



Current Avg Price =
CALCULATE (
    SUMX (
        SUMMARIZE (
            'Invoice Variance',
            'Invoice Variance'[item],
            "CurAvgPrice",
                DIVIDE (
                    SUM ( 'Invoice Variance'[ext_price] ),
                    SUM ( 'Invoice Variance'[volume] ),
                    0
                )
        ),
        [CurAvgPrice]
    ),
    FILTER (
        ALL ( 'Invoice Variance'[inv_date] ),
        'Invoice Variance'[inv_date] <= MAX ( 'Current'[Date] )
            && 'Invoice Variance'[inv_date] >= MIN ( 'Current'[Date] )
    )
)

 

Has you can see below the lines that you refer are picking up the values:

 

All others have only a single value previous or current. This is impacted by a slicer with the dates of current with 2021 dates.

 

What is happening is that since you were using the full date table when you compare the current date 2020 with previous year 2019 you are not getting values because your data starts in 2020, on the other and for the current values when you search for the current value on 2021 you are not getting values because your data is of 2020.. (not sure if I made it clear)

 

Check PBIX file attach.