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.
In the screenshot below you can see my Power BI page. I want to calculate the days between two dates. I want to have the number of days from VALIDFROM to Tot Datum. But when the Tot Datum is not within the filter range I want it to calculate the date difference until the maximum date from the filter. In this example, I show all records in the period 1-1-2020 until 31-10-2020. The measures are calculated as follows:
- Test = MAX('Dim_Datum Periode'[Datum]), where 'Dim_Datum Periode'[Datum] is also in the filter
- Tijd in Leegstand = DATEDIFF('PMCCONTRACT'[VALIDFROM],[Test],DAY)+1
In the table below, the Test measure gives the right value for each record. However, when Tijd in Leegstand is calculated only the number of days is calculated in the right way when Test > Tot Datum, but not when Test < Tot Datum. For example, see the record underlined in red. Here, the Tijd in Leegstand should be 61 and not 77. This is strange to me because the measure Test is right in this row. How can I solve this problem?
Thanks for you reply. VALIDFROM is a column from the contracts table while Tijd in Leegstand is a measure. I modified your column somewhat by:
For your syntax to work, either VALIDFROM is a measure or Tijd in Leedstand is a DAX column. I suspect the later. Your columns probably should be done as measures instead. But for the one you are trying to respond to the slicer, you could use this measure expression
Tijd in Leegstand =
VAR maxdate =
MAX ( 'Dim_Datum Periode'[Datum] )
VAR totdatumdate =
MAX ( 'PMContract'[Tot Datum] )
VAR comparedate =
IF (
maxdate > totdatumdate,
totdatumdate,
maxdate
)
RETURN
DATEDIFF (
MAX ( 'PMContract'[VALIDFROM] ),
comparedate,
DAY
) + 1
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I understand that. However, using my measure Tijd in Leegstand does not display the correct values as I showed in my initial post. How can I adjust my measure such that it shows me the correct values in the table?
@Anonymous , A column can not use slicer value. The measure can , So think in term of measure. You might have to force a row context if needed
Alright. And there is no way of doing what I want by using DATEDIFF in the contracts table using a date column that changes dynamically with the slicer?
@Anonymous , this seems like a column. You can not use slicer for column
Create a measure like
Tijd in Leegstand = DATEDIFF(max('PMCCONTRACT'[VALIDFROM]),[Test],DAY)+1
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |