Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
unkCandy
Helper I
Helper I

Calculate variance between actual and target On or Before a target date

Hello community, 

I am beginner in power bi, and I would like your help to figure out my problem. 

I have two tables linked by id: 

  • the first table is the id, target date, and target value 
  • the second table is the id, actual date, and actual value

I want to calculate the variance between the target and actual according to the target date and actual date. 

here is my table: 

unkCandy_0-1668419721211.png

for example for id 1: I want to know the variance between the sum of actual values that happened on/before the target date: 

So before 30-march I have the three orange values, so the result will sum(50,3000,2000)- 2000

My result will be: 

unkCandy_1-1668419936774.png

Is there any way to implement this on power bi? 

Thank you for your help.

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @unkCandy ,

 

Please try:

First, create a measure:

variance =
VAR _a =
    MAXX (
        FILTER (
            ALL ( 'target table' ),
            [Id] = MAX ( 'target table'[Id] )
                && [target date] < MAX ( 'target table'[target date] )
        ),
        [target date]
    )
RETURN
    CALCULATE (
        SUM ( 'actual table'[actual] ),
        FILTER (
            'actual table',
            [actual date] > _a
                && [actual date] <= MAX ( 'target table'[target date] )
        )
    )
        - SUM ( 'target table'[target] )

 Then apply it to the table visual:

vjianbolimsft_0-1668480102880.png

Final output:

vjianbolimsft_1-1668480120938.png

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-jianboli-msft
Community Support
Community Support

Hi @unkCandy ,

 

How to get the target value based on the actual date? Can you please explain it in more detail? Is it based on the nearest target date to the actual date? Or is it before or after the actual date?

Please provide me with more details about the logic.

 

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-jianboli-msft
Community Support
Community Support

Hi @unkCandy ,

 

Please try:

First, create a measure:

variance =
VAR _a =
    MAXX (
        FILTER (
            ALL ( 'target table' ),
            [Id] = MAX ( 'target table'[Id] )
                && [target date] < MAX ( 'target table'[target date] )
        ),
        [target date]
    )
RETURN
    CALCULATE (
        SUM ( 'actual table'[actual] ),
        FILTER (
            'actual table',
            [actual date] > _a
                && [actual date] <= MAX ( 'target table'[target date] )
        )
    )
        - SUM ( 'target table'[target] )

 Then apply it to the table visual:

vjianbolimsft_0-1668480102880.png

Final output:

vjianbolimsft_1-1668480120938.png

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello, 

I made a mistake explaining, I would like to calculate actual value -target value using target date. 

I will explain, I want to extract the target value according to the actual date. 

Here is an example: 

My actual table is: 

unkCandy_0-1668518631371.png

and my target table is as follows: 

unkCandy_1-1668518660508.png

I want to calculate actual-target values as the target can be retreived from the target table according to actual date. For example:

  • For id=1, first row I have the date 31-Jan so I should the target value of the date 31-march
  • for id=1, forth row, i have the date 30-Sep so i should get the target value of the date 30-Aug 

My result will look like:

unkCandy_2-1668518720666.png

I hope you can help, 

thank you again.

 

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors