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. ...
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.

     



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.