Forum Discussion

sureshsonti's avatar
sureshsonti
Icon for Helper II rankHelper II
8 years ago
Solved

TODAY function

Hello, I have a table with Order Date and Scheduled Qty. I created a measure to display today's orders using the TODAY function in DAX. But after 7 PM the report shows tomorrow's orders.   Please ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi sureshsonti,

     

    The Service uses UTC time. Maybe you can try the formula below.

    Yardage =
IF (
    UTCNOW () = NOW (),
    CALCULATE (
        SUM ( YORX[Qty] ),
        FILTER ( 'YORX', 'YORX'[order_date] = DATEVALUE ( UTCNOW () + TIME ( 5, 0, 0 ) ) )
    ),
    CALCULATE (
        SUM ( YORX[Qty] ),
        FILTER ( 'YORX', 'YORX'[order_date] = TODAY () )
    )
)

     

sureshsonti's avatar
sureshsonti
Icon for Helper II rankHelper II
8 years ago

I am not able to attach a word file with screenshots.

 

My data has Order Date, and Units Produced which is Yardage. I just named it differently. But its the same field and I made a copy of the original field. Based on the Order Date power bi calculates Today's Yardage, MTD Yardage, and YTD Yardage.

 

On my workstation Power BI Desktop shows Today's Volume for Today till 11:59 PM and it changes to tomorrow at 12:00 AM which is what I want. But when I publish this data to Power BI Services after 7 PM on the same day the Today's Volume shows BLANK which means we didnt delivery or produce anything yet because that data is for Tomorrow. I am attaching a word document with screen shots

 

I will try to attach the screen shots

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi sureshsonti,

 

The Service uses UTC time. Maybe you can try the formula below.

Yardage =
IF (
    UTCNOW () = NOW (),
    CALCULATE (
        SUM ( YORX[Qty] ),
        FILTER ( 'YORX', 'YORX'[order_date] = DATEVALUE ( UTCNOW () + TIME ( 5, 0, 0 ) ) )
    ),
    CALCULATE (
        SUM ( YORX[Qty] ),
        FILTER ( 'YORX', 'YORX'[order_date] = TODAY () )
    )
)

 

  • sureshsonti's avatar
    sureshsonti
    Icon for Helper II rankHelper II
    8 years ago

    Let me try this and see if this works. Will email you back in the evening after 7 pm. Thanks for your help again. Really appreciate it.

    • sureshsonti's avatar
      sureshsonti
      Icon for Helper II rankHelper II
      8 years ago

      As of now I am getting today's orders value in Yardage column. I have to wait till 7 pm and see if it still shows today's values. I really hope it still shows todays values after 7 pm. Again thank you very much.

  • sureshsonti's avatar
    sureshsonti
    Icon for Helper II rankHelper II
    8 years ago

    Hi,

    I made a small change in the formula. I used - (Minus Sign) instead of + (Plus Sign). It worked great. Thanks for your help. Really appreaciate it.

     

     

  • sureshsonti's avatar
    sureshsonti
    Icon for Helper II rankHelper II
    7 years ago

    Hello,

    How can I use this formula to compare Today vs Last Year Same Day.  I want to create 2 measures one for Today and the other for Last Year same day 

     

    Example 12/17/2018 vs 12/17/2017

     

    Thanks

     

     

    Yardage =
IF (
    UTCNOW () = NOW (),
    CALCULATE (
        SUM ( YORX[Qty] ),
        FILTER ( 'YORX', 'YORX'[order_date] = DATEVALUE ( UTCNOW () + TIME ( 5, 0, 0 ) ) )
    ),
    CALCULATE (
        SUM ( YORX[Qty] ),
        FILTER ( 'YORX', 'YORX'[order_date] = TODAY () )
    )
)

     

  • sureshsonti's avatar
    sureshsonti
    Icon for Helper II rankHelper II
    7 years ago

     

     

      

    These are my fields in my Data Set. I am trying to avoid filters on the report.

    Order Date
    Year
    Month
    Location
    Units Sold

     

    1. I want to extract Units Sold Month To Date of Current Year based on Order Date
    2. I want to extract Units Sold Month To Date (Last Year) based on the Order Date

     

    Want to compare Units Sold Month To Date of Current Month and Last Year Month To Date of the Same Month using DAX formula. Trying to avoid filters on the report

     

    Example December 2018 vs December 2017

     

    Thanks for your help

     

    Thanksdata.JPG

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      7 years ago

      Hi sureshsonti,

       

      I would suggest you create a new post in this forum with a sample that can be copied or downloaded. It's a new topic. 

       

      Best Regards,
      Dale