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
greatzt
Helper II
Helper II

Calculate difference against the value of a previous period selected by user

I need to display  the latest value and the value reported at a date selected by user and calculate their difference.

The following is my data set.  Three ID report values at different dates. exampledata.PNG

what I want is the following table:

IDValuePrev ValueDiff
A392910
B391920
C503218

 

Here the Value is data reported on the latest date (2/3/2018), and Prev Value is the value reported on 1/7/2018, which is the date chosen by the user by selecting the following slicer. I tried differnet funcation such LOOKUPVALUE and CACLUATE, but none of them is workong. Can some one give me some ideas about how to implement it?

Prev Date Slicer.PNG

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

Hi @greatzt,

Please create new date table only include date by clicking "New Table" under Modeling on Home page. There is no relationship between date and your fact table.

1.PNG

Create a slicer including Date[Date], create three measure, you will get expected result.

LatestDate_Value =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( Table1, Table1[Date] = MAXX ( ALL ( 'Date'[Date] ), 'Date'[Date] ) )
)


Pre_Value =
IF (
    ISFILTERED ( 'Date'[Date] ),
    CALCULATE (
        SUM ( Table1[Value] ),
        FILTER ( Table1, Table1[Date] = SELECTEDVALUE ( 'Date'[Date] ) )
    ),
    [LatestDate_Value]
)

Diff = [LatestDate_Value]-[Pre_Value] 

 

Please see expected result as follows, you can download attachment file for more details.

1.PNG

Best Regards,
Angelia

View solution in original post

3 REPLIES 3
v-huizhn-msft
Employee
Employee

Hi @greatzt,

Please create new date table only include date by clicking "New Table" under Modeling on Home page. There is no relationship between date and your fact table.

1.PNG

Create a slicer including Date[Date], create three measure, you will get expected result.

LatestDate_Value =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( Table1, Table1[Date] = MAXX ( ALL ( 'Date'[Date] ), 'Date'[Date] ) )
)


Pre_Value =
IF (
    ISFILTERED ( 'Date'[Date] ),
    CALCULATE (
        SUM ( Table1[Value] ),
        FILTER ( Table1, Table1[Date] = SELECTEDVALUE ( 'Date'[Date] ) )
    ),
    [LatestDate_Value]
)

Diff = [LatestDate_Value]-[Pre_Value] 

 

Please see expected result as follows, you can download attachment file for more details.

1.PNG

Best Regards,
Angelia

@v-huizhn-msft thank you for the response.  it is working.  However, now I need to give user the choice to choose which date is current date. Bascailly,  there are two slicers  to select two dates to compare values. I tried using your methond but it is not working.  After reatding https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/, I almost got what I want except the difference between those values.  This should be a simple calculated column, but the value is not correct. This is the https://1drv.ms/u/s!AtUXUkmpQTxSqnqA_fsXtmk5Vlie . I create three diff column, but none of them give me correct result. 

Hi @greatzt,

For this thread, please mark it as answer if it's working. There is one issue in a thread. Please create a new one if you have another problem. Thanks for understanding.

Best Regards,
Angelia

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.