Forum Discussion
split rows into column on date column
Hi Team,
We have large amount of data on each date . below is just sample source data.
| ScriptName | Value | Date |
| Tata Motors | 250 | 5/3/2022 |
| Relience | 1400 | 5/3/2022 |
| Infosys | 1500 | 5/3/2022 |
| Bajaj | 2000 | 5/3/2022 |
| Dell | 140 | 5/3/2022 |
| Tata Motors | 260 | 5/2/2022 |
| Relience | 1500 | 5/2/2022 |
| Infosys | 1300 | 5/2/2022 |
| Bajaj | 2010 | 5/2/2022 |
| Dell | 145 | 5/2/2022 |
| Tata Motors | 275 | 5/1/2022 |
| Relience | 1450 | 5/1/2022 |
| Infosys | 1200 | 5/1/2022 |
| Bajaj | 2030 | 5/1/2022 |
| Dell | 135 | 5/1/2022 |
| Tata Motors | 280 | 4/31/2022 |
| Relience | 1505 | 4/31/2022 |
| Infosys | 1600 | 4/31/2022 |
| Bajaj | 2100 | 4/31/2022 |
| Dell | 148 | 4/31/2022 |
i want only today date(Today) values and yesterday(t-1) and day befor yesterday(T-2) and difference today and t-2
output:-
| Scripname | T-2 | T-1 | Today | Difference Today and T-2 |
| Bajaj | 2030 | 2010 | 2000 | 30 |
| Dell | 135 | 145 | 140 | -5 |
| Infosys | 1200 | 1300 | 1500 | -300 |
| Relience | 1450 | 1500 | 1400 | 50 |
| Tata Motors | 275 | 260 | 250 | 25 |
Hi, anshenterprice
Please try the following methods.
Measure:
Today = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), [Date] = MAX ( 'Table'[Date] ) && [ScriptName] = SELECTEDVALUE ( 'Table'[ScriptName] ) ) )T-1 = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), [Date] = MAX ( 'Table'[Date] ) - 1 && [ScriptName] = SELECTEDVALUE ( 'Table'[ScriptName] ) ) )T-2 = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), [Date] = MAX ( 'Table'[Date] ) - 2 && [ScriptName] = SELECTEDVALUE ( 'Table'[ScriptName] ) ) )Difference Today and T-2 = [T-2]-[Today]Is this the result you were hoping for?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
I changed some of you data to todays date for illustration purposes
you can use Today() - 1 on your T1 measure and so on
Thank you NickolajJessen
It should not be always today's date may be rather we can use max date . i tried but getting error
and also please help on the lastcolumn ie. Difference Today and T-2Can you elaborate what is not working and include screenshots of what you tried - measures and such
Hi, anshenterprice
Please try the following methods.
Measure:
Today = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), [Date] = MAX ( 'Table'[Date] ) && [ScriptName] = SELECTEDVALUE ( 'Table'[ScriptName] ) ) )T-1 = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), [Date] = MAX ( 'Table'[Date] ) - 1 && [ScriptName] = SELECTEDVALUE ( 'Table'[ScriptName] ) ) )T-2 = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), [Date] = MAX ( 'Table'[Date] ) - 2 && [ScriptName] = SELECTEDVALUE ( 'Table'[ScriptName] ) ) )Difference Today and T-2 = [T-2]-[Today]Is this the result you were hoping for?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
