Forum Discussion

_Nick_'s avatar
_Nick_
Regular Visitor
1 year ago
Solved

Power BI Report Subscription "Include Today" Date Filter Bug

My company, located in the Toronto timezone (-5) has a report that goes out at shift end at 7:00 PM standard local time. The problem is, this is 12:00 AM UTC, which shifts the date to the next day.

 

When I view the report myself, everything is fine. This was the date, showing the correct value, at the time the report email went out (8:15 PM standard local time). The rest of the report was populated with today's data.

 

In the email, however, the report has tomorrow's date, and all the charts are empty.

At the bottom of the report, it indicates:

 

Last week when it was still daylight savings time, I had the report set to email at 7:30 PM local daylight time, which is 11:30 PM UTC the same day, so everything was working fine; in this case I had the "include today" option of the date filter checked. After we transitioned from Daylight Savings Time back to Standard Time, the reports came back attempting to report "the next day". Deselecting the "include today" option SHOULD have made things work again, but it doesn't appear to be responding properly in the context of the subscription email report creation.

 

Need Help
  • Vinay_eshwara's avatar
    Vinay_eshwara
    1 year ago

    Hi, _Nick_ 
    Instead of relying on Power Bi's  date filters we can create a custom DAX measure to dynamically adjust to the timezone.(UTC or Local timezone).
    Here is a step by step procedure .
    Step 1 : Creating a 'TodayToronto' Measure

    TodayToronto =
        VAR CurrentUTC = UTCNOW()
        VAR TimeOffset =
            IF(
                MONTH(CurrentUTC) > 3 && MONTH(CurrentUTC) < 11,
                -4,  
                -5  
            )
        RETURN
            DATE(
                YEAR(CurrentUTC),
                MONTH(CurrentUTC),
                DAY(CurrentUTC + TimeOffset / 24)
            )


    This measure calculates Toronto’s current date by adding the TimeOffset to the current UTC time.

    Step 2: Create a 'IsTodayToronto' Measure for Filtering Today’s Data

     
    IsTodayToronto =
        IF(
            DATE(
                YEAR(MAX(SalesData[Date and Time])),
                MONTH(MAX(SalesData[Date and Time])),
                DAY(MAX(SalesData[Date and Time]))
            ) = [TodayToronto],
            1,
            0
        )

    This measure filters data based on the date from 'TodayToronto' measure like '1' for dates in 'TodayToronto'
    and '0' for other dates.

    Now applying the measures and filters in Power bi desktop.
    I created a sample dataset for representing and applied the measures and filters like below images.
    Dataset 

     

    Visualizing:

    This is before applying 'IsTodayToronto' measure in the Filters,

    After applying 'IsTodayToronto' measure and condition is 1 in the Filters

    As you can see the measure is filtering todays date data .


    Now you can Test the Solution with Power BI’s Email Subscription by setting up the subscription Email and scheduling it with your desired time(eg.7:00 P.M Toronto time).
    Then you can verify the data.

    Please give a kudos and accept it as solution if it meets your goals.
    Thanks,
    Vinay.

     



1 Reply

  • Vinay_eshwara's avatar
    Vinay_eshwara
    Frequent Visitor
    1 year ago

    Hi, _Nick_ 
    Instead of relying on Power Bi's  date filters we can create a custom DAX measure to dynamically adjust to the timezone.(UTC or Local timezone).
    Here is a step by step procedure .
    Step 1 : Creating a 'TodayToronto' Measure

    TodayToronto =
        VAR CurrentUTC = UTCNOW()
        VAR TimeOffset =
            IF(
                MONTH(CurrentUTC) > 3 && MONTH(CurrentUTC) < 11,
                -4,  
                -5  
            )
        RETURN
            DATE(
                YEAR(CurrentUTC),
                MONTH(CurrentUTC),
                DAY(CurrentUTC + TimeOffset / 24)
            )


    This measure calculates Toronto’s current date by adding the TimeOffset to the current UTC time.

    Step 2: Create a 'IsTodayToronto' Measure for Filtering Today’s Data

     
    IsTodayToronto =
        IF(
            DATE(
                YEAR(MAX(SalesData[Date and Time])),
                MONTH(MAX(SalesData[Date and Time])),
                DAY(MAX(SalesData[Date and Time]))
            ) = [TodayToronto],
            1,
            0
        )

    This measure filters data based on the date from 'TodayToronto' measure like '1' for dates in 'TodayToronto'
    and '0' for other dates.

    Now applying the measures and filters in Power bi desktop.
    I created a sample dataset for representing and applied the measures and filters like below images.
    Dataset 

     

    Visualizing:

    This is before applying 'IsTodayToronto' measure in the Filters,

    After applying 'IsTodayToronto' measure and condition is 1 in the Filters

    As you can see the measure is filtering todays date data .


    Now you can Test the Solution with Power BI’s Email Subscription by setting up the subscription Email and scheduling it with your desired time(eg.7:00 P.M Toronto time).
    Then you can verify the data.

    Please give a kudos and accept it as solution if it meets your goals.
    Thanks,
    Vinay.