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.
The date range of the data falls between the following period: 29/09/2018 < date < 09/08/2022.
So dates beyond this interval should return Blank().
I want the measure for each month/year to display the value of the same day as the last day of the above period.
For example:
on December/2021, I want the value for the 09/12/2021.
on September/2019, I want the value for the 09/09/2019.
Always the 9th.
This is the DAX code I wrote
MEASURE SameDaysPreviousYear =
VAR LastDateAll = CALCULATE ( MAX('Date'[Date] ), ALL ('Date') )
VAR FirstDateAll = CALCULATE ( MIN ( 'Date'[Date] ), ALL ( 'Date' ) )
VAR LastDayOfReport = DAY (LastDateAll )
VAR CalcDates1 =
DATESINPERIOD (
'Date'[Date],
MIN ( 'Date'[Date] ),
LastDayOfReport,
DAY
)
VAR CalcDates2 =
DATESINPERIOD (
'Date'[Date],
MIN ( 'Date'[Date] ),
LastDayOfReport - 1,
DAY
)
VAR StorageMonthValue1 =
CALCULATE (
SUM ( 'GasBBActualFlowStorage'[HeldInStorage] ),
CalcDates1
)
VAR StorageMonthValue2 =
CALCULATE (
SUM ( 'GasBBActualFlowStorage'[HeldInStorage] ),
CalcDates2
)
VAR Result = StorageMonthValue1 - StorageMonthValue2
RETURN Result
To retrieve the following result:
All the values correspond to the 9th of that month.
How do I return Blank() for the values beyond the date range?
Solved! Go to Solution.
Thanks for your reply, @PurpleGate .
I wonder if your formula would be enough since it doesn't seem to exclude dates before 29/09/2018.
Moreover, LastDateAll is not necessarily TODAY().
Anyway, I found a solution.
I had to rewrite VAR Result
VAR Result =
IF (
OR (
MAX('Date'[Date]) < FirstDateAll,
MIN('Date'[Date]) > LastDateAll
),
BLANK(),
StorageMonthValue1 - StorageMonthValue2
)
To which I end up getting the sought result:
Create this column and add that column either as a page filter or a visual filter
DateLessThanToday = 'DateTable'[Date] <= TODAY()
Set it as "true"
Thanks for your reply, @PurpleGate .
I wonder if your formula would be enough since it doesn't seem to exclude dates before 29/09/2018.
Moreover, LastDateAll is not necessarily TODAY().
Anyway, I found a solution.
I had to rewrite VAR Result
VAR Result =
IF (
OR (
MAX('Date'[Date]) < FirstDateAll,
MIN('Date'[Date]) > LastDateAll
),
BLANK(),
StorageMonthValue1 - StorageMonthValue2
)
To which I end up getting the sought result:
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 |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |