Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I need to create a visual with calculate the sum of AmountCY and AmountLY, withrevenue year and month. The data has Amount, IDs, RDate, IntDate. I have to calculate sum of Amount for previous year(based on RDate column) for only those IDs which were present in selected date range and on a particular IntDate. I have calculated two measures AmountCY and AmountLY.
where Rdate range =01/01/2020 to 10/30/2020
I need AmountLY values only for Id =110 . As the Rdate for Previous year will be =01/01/2019 to 10/30/2019. and only this id was present in selected Rdate range(current year range)
AmountLY | ID | AmountLY | RDate | IDate |
10000 | 100 | 5/27/2020 | 7/26/2020 | |
102 | 126.3 | 9/6/2019 | 7/26/2020 | |
110 | 3000 | 3/30/2019 | 7/26/2020 | |
3000 | 110 | 4/18/2020 | 7/26/2020 | |
122 | 7.03 | 7/12/2019 | 7/26/2020 | |
122 | 1500 | 7/13/2019 | 7/26/2020 | |
132 | 1052.5 | 5/20/2019 | 7/26/2020 |
@sydv , You can join RDate with date table and use that as slicer
Sum(Table[AmountCY]) is for that range
Year behind = CALCULATE(Sum(Table[AmountCY]),dateadd('Date'[Date],-1,Year))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Hi,
Thanks for points,
But this would again give the same values as I have in AmountLY measure.
while I need values against only those ID's in Year Behind measure which are present in current year. i.e. AmountLY for ID -110 only.
Regards
User | Count |
---|---|
51 | |
46 | |
20 | |
16 | |
14 |
User | Count |
---|---|
108 | |
51 | |
30 | |
20 | |
18 |