Calculate change in measure value period vs. period change

[Anonymous]

I have a division measure that takes Delivery cost divided by a Sales Cost to arrive at a %. I want to calculate the change in % points period vs. period. How do I do this? It feels like I need a measure of a measure?

Using the below example.

Period 1 measure result is 15.1%.

Period 2 measure result is 11.9%

I want to subtract the two periods 15.1% - 11.9% = 3.2%

The visual right now is looking at Quarter 1, but I will eventually make more visuals that may display the full year, one month, or a week. So, I would like to this to be dynamic. 

Note: here is the measure I'm currently using:

CA Amt (Allocated) divided by Delivery USD =

DIVIDE(

    SUM('Deliveries'[CA Amt (Allocated)]),

    SUM('Deliveries'[Delivery USD])

)

[Anonymous]

Hi @Anonymous ,Please follow these steps:

1. Select CA and USD columns and then Use "unpivot" in Query Editor,then data will be transformed like this:

2. Use the formula I refered before,then the output looks like this:

Here is my pbix link:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/stephent01_qiuyunus_onmicrosoft_com/EZvMgo6bBmhFvf-JtUoxehwBXtWkWNyyVXEql_1gjPNM0g?e=8bCLoo 

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[Anonymous]

Hi @Anonymous ,

According to my understanding ,you want to calculte the percentage's change based on the same day/month/quarter between years,right?

Here is my data sample:

Then try to use the following formula:

divide values =
DIVIDE (
    CALCULATE (
        SUM ( 'Delivery date'[Value] ),
        FILTER ( 'Delivery date', 'Delivery date'[CA&USD] = "CA" )
    ),
    CALCULATE (
        SUM ( 'Delivery date'[Value] ),
        FILTER ( 'Delivery date', 'Delivery date'[CA&USD] = "USD" )
    )
)

////Year-Quarter for matrix column

year-quarter =
'Delivery date'[Date].[Year] & " " & 'Delivery date'[Date].[Quarter]

totalResults =
IF (
    HASONEVALUE ( 'Delivery date'[year-quarter] ),
    IF (
        HASONEVALUE ( 'Delivery date'[CA&USD] ),
        SUM ( 'Delivery date'[Value] ),
        [divide values]
    ),
    CALCULATE (
        [divide values],
        FILTER (
            'Delivery date',
            'Delivery date'[Date].[Year] = 2019
                && 'Delivery date'[Date].[QuarterNo] = 1
        )
    )
        - CALCULATE (
            [divide values],
            FILTER (
                'Delivery date',
                'Delivery date'[Date].[Year] = 2020
                    && 'Delivery date'[Date].[QuarterNo] = 1
            )
        )
)

My visualizations look like this:

Is the result what you want? If not, please upload some data samples and expected output.
Please do mask sensitive data before uploading.

Best Regards,
Eyelyn Qin

[Anonymous]

Thanks so much. I think yours might work but I'm wondering if there is a simplier approach?

I've taken your example data set and created an example model with link below. I need to add one additional column to show what I was trying to accomplish.

https://1drv.ms/u/s!AvCirXUmRp-JhbY7bLCDvl6fm6HZNA?e=gPcpmv

Using example data set I'm trying to subtract 2.67% (Measure from Q1) - 0.30% (Measure from Q2) = -2.07% difference in quarters

[amitchandak]

@Anonymous , refer to this, how do this minimum from GuyinaCube

https://www.youtube.com/watch?v=vlnx7QUVYME

Power BI — YTD Questions — Time Intelligence 1–5
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD Questions — Time Intelligence 2–5
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD Questions — Time Intelligence 3–5
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD Questions— Time Intelligence 4–5
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
Power BI — Day Intelligence Questions — Time Intelligence 5–5
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739