Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have to calculate running total based on created date. I have one table where all the transaction is stored and other table has created date along with quarter. I used concatenate to create a key (created date + created quarter) in transaction as well as date table. Using this key I am getting day of quarter from date table. Now I am calculating running total for Previous quarter and passing dayofQtr as filter but getting wired result.
PQ_Rev =
CALCULATE ([QTD],
FILTER ( ALL(Revenue_Table), Revenue_Table[Fiscal Quarter] = "FY18-Q3" && Revenue_Table[DayofQtr] <= [DayOfPQ])
)
This formula return incorrect number . DayofPQ is of same data type as “Revenue_Table[DayofQtr]”
PQ_Rev_Test =
CALCULATE ([QTD],
FILTER ( ALL(Revenue_Table), Revenue_Table[Fiscal Quarter] = "FY18-Q3" && Revenue_Table[DayofQtr] <= 24)
)
This formula return correct result. I just pass actual day (24) here.
DayOfPQ is derived from created date (same date last quarter - Start of the last quarter )
Can anyone help how to get correct result using dynamic filter (DayOfPQ)
Solved! Go to Solution.
May be you just need to store the MEASURE value in a variable first
Try this
PQ_Rev_Test = VAR myvalue = [DayOfPQ] RETURN CALCULATE ( [QTD], FILTER ( ALL ( Revenue_Table ), Revenue_Table[Fiscal Quarter] = "FY18-Q3" && Revenue_Table[DayofQtr] <= myvalue ) )
May be you just need to store the MEASURE value in a variable first
Try this
PQ_Rev_Test = VAR myvalue = [DayOfPQ] RETURN CALCULATE ( [QTD], FILTER ( ALL ( Revenue_Table ), Revenue_Table[Fiscal Quarter] = "FY18-Q3" && Revenue_Table[DayofQtr] <= myvalue ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |