The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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:
Table of results
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:
Yes!
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:
Yes!
User | Count |
---|---|
28 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
36 | |
14 | |
12 | |
7 | |
7 |