Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
what I want is the following table:
ID | Value | Prev Value | Diff |
A | 39 | 29 | 10 |
B | 39 | 19 | 20 |
C | 50 | 32 | 18 |
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?
Solved! Go to Solution.
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.
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.
Best Regards,
Angelia
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.
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.
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |