Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

How to return a Blank calculation in a Date matrix

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 resultsTable of results

 

All the values correspond to the 9th of that month.

How do I return Blank() for the values beyond the date range?

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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!Yes!

View solution in original post

2 REPLIES 2
PurpleGate
Resolver III
Resolver III

Create this column and add that column either as a page filter or a visual filter

 

DateLessThanToday = 'DateTable'[Date] <= TODAY()

Set it as "true"

 

Anonymous
Not applicable

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!Yes!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.