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.
Good afternoon:
I am trying to make a measure that reflects in a table the total cost Budgeted by day, month and year ONLY UNTIL the last date of the "Real" table. I have (among others) the following tables in my data model:
- Calendar
- Ppto: Table of costs budgeted from 01/01/2019 to 31/12/2019
- Real: Table of Actual Accounting costs from 01/01/2019 to 28/06/2019
The result that I intend to obtain must be equal to what the following measure would give me:
Total = calculate(sum(Ppto[importe]);filter(Calendario;Calendario[Date]<=VALUE(28/06/2019)))
The question is: With what dax expression can I substitute VALUE (28/06/2019) for the FINAL DATE of the "Real" table ? (28/06/2019 is the last date of the "Real" table).
For example, if I use this measure: "calculate (sum (Ppto [importe); filter (Calendario; Calendario [Date] <= max (Real [date]))" the result I get is incorrect because in the table "Real" there are not all the dates that exist in the" Calendario "table.
Thanks in advance,
José Luis
Solved! Go to Solution.
"The question is: With what dax expression can I substitute VALUE (28/06/2019) for the FINAL DATE of the "Real" table ? (28/06/2019 is the last date of the "Real" table)."
Well, that's rather simple:
[Very Last Date in Real] := calculate( max( Real[Date] ), all( Real ) )
and here's how you'd use it:
var __lastDateInReal = [Last Date in Real] return calculate ( sum ( Ppto[importe] ); KEEPFILTERS( Calendario[Date] <= __lastDateInReal ) )
if you want to intersect filters on Calendario that are coming from the outer context... and this is probably what you want to do.
Best
Darek
"The question is: With what dax expression can I substitute VALUE (28/06/2019) for the FINAL DATE of the "Real" table ? (28/06/2019 is the last date of the "Real" table)."
Well, that's rather simple:
[Very Last Date in Real] := calculate( max( Real[Date] ), all( Real ) )
and here's how you'd use it:
var __lastDateInReal = [Last Date in Real] return calculate ( sum ( Ppto[importe] ); KEEPFILTERS( Calendario[Date] <= __lastDateInReal ) )
if you want to intersect filters on Calendario that are coming from the outer context... and this is probably what you want to do.
Best
Darek
Hello, Thank you for the help.
In my case, I want to get the max date depending on the filter and go back a year.
For example, if the selected date on the Real date filter is 6/22/2022. I want to filter another customer table where customer date equals 6/22/2021.
Hi all,
How i can use formula which help me make sameperiod last month.
Now i use this, but it's error (red text)
Hello,
What about this?
Hello:
Thank you very much for your answer. The measurement you indicate does not work correctly, I try to explain it with the following image:
Not sure if I understand you correctly, but the following measure is also displaying values for periods that don't exisit in the actuals table;
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |