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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Variance Measure Between Different Shifts

Hi there!

 

I have a column of usage and want to calculate the difference as shown in this table of my data:

 

DateUsageCrewDifference
1/1/20181a 
1/7/20182a-1
1/1/20181b 
1/2/20185b-4
1/1/20185c 
1/6/201815c-10
1/9/20188a 
1/10/201810a-2

 

However, I want this as a DAX measure so when I make a table visual it comes out like so:

 

DateUsageCrewDifference
1/7/20182a-1
1/2/20185b-4
1/6/201815c-10
1/10/201810a-2

 

 

Any help is apreciated!

 

1 ACCEPTED SOLUTION

Hi @Anonymous,

 

Please check out the demo in the attachment. 

1. Add an index. 

2. Add a custom column in the Query Editor.

if [Index] = 1 then 1 else if #"Added Index"{[Index] - 2}[Crew] = [Crew] then [Index] - 1 else [Index]

3. Create two measures.

UsageMeasure =
CALCULATE (
    SUM ( Table1[Usage] ),
    FILTER ( 'Table1', 'Table1'[Date] = MAX ( 'Table1'[Date] ) )
)
Measure =
VAR firstValue =
    CALCULATE (
        SUM ( Table1[Usage] ),
        FILTER ( 'Table1', 'Table1'[Date] = MIN ( 'Table1'[Date] ) )
    )
VAR secondValue =
    CALCULATE (
        SUM ( Table1[Usage] ),
        FILTER ( 'Table1', 'Table1'[Date] = MAX ( 'Table1'[Date] ) )
    )
RETURN
    firstValue - secondValue

img

Best Regards,
Dale

Community Support Team _ Dale
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
affan
Solution Sage
Solution Sage

Hi @Anonymous,

 

If you have the data in the table as showin the 1st table in your post. Then you can get the result without creating a measure.

Simply add the fields to a table visual and in the "Visual level Filter" apply a filter to the Difference column as 'Is not blank'

 

 

Isnotblank.png

 

Usage Difference.png

 

 

For further reference you can access the pbix file below.

https://www.dropbox.com/s/k1rb4w9n0ibf0df/Difference%20nblank.pbix?dl=0

 

 

Please mark this post as an accepted solution if this helped you.

 

Regards,

Affan

Anonymous
Not applicable

Thank you for your reply @affan, No, I do not have the data shown in the first table. I am trying to specify that the difference needs to be linked to the second crew value when calculated via measure

Hi @Anonymous,

 

Please check out the demo in the attachment. 

1. Add an index. 

2. Add a custom column in the Query Editor.

if [Index] = 1 then 1 else if #"Added Index"{[Index] - 2}[Crew] = [Crew] then [Index] - 1 else [Index]

3. Create two measures.

UsageMeasure =
CALCULATE (
    SUM ( Table1[Usage] ),
    FILTER ( 'Table1', 'Table1'[Date] = MAX ( 'Table1'[Date] ) )
)
Measure =
VAR firstValue =
    CALCULATE (
        SUM ( Table1[Usage] ),
        FILTER ( 'Table1', 'Table1'[Date] = MIN ( 'Table1'[Date] ) )
    )
VAR secondValue =
    CALCULATE (
        SUM ( Table1[Usage] ),
        FILTER ( 'Table1', 'Table1'[Date] = MAX ( 'Table1'[Date] ) )
    )
RETURN
    firstValue - secondValue

img

Best Regards,
Dale

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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