Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamic yesterday until specific time and today date

Hi all I need a measure to show dynamic yesterday and today date.   The logic is show yesterday's date until today 3:00 PM (Australian time) and after this time show today's date.   Then I will ...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, Anonymous 

     

    According to your description, I think you can easily create a calculated column to show the results.

    Like this:

    Column =
VAR a =
    HOUR ( 'Table'[Column1] )
RETURN
    IF (
        a <= 15,
        DATE ( YEAR ( 'Table'[Column1] ), MONTH ( 'Table'[Column1] ), DAY ( 'Table'[Column1] ) ) - 1,
        DATE ( YEAR ( 'Table'[Column1] ), MONTH ( 'Table'[Column1] ), DAY ( 'Table'[Column1] ) )
    )

     

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
5 years ago

Anonymous  Do you have a dimdate table? I would add a current date offset to this table- that will enable you to filter for today, yesterday, two days ago, etc.

 

In your Fact/Transaction table you can use Power Query to add a new column that converts the datetime into Date only based on the time of day. This will likely require a few calculated columns and conditional statements, or manual M code. Let us know if you have ability to use calc columns in Power Query and we can provide more specific formulas for that if needed. 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi AllisonKennedy

 

Yes I have a DIM date and I also have access to the power query

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous  that's great- has adding a column to date table and creating the relationship helped solve your problem? If not, please advise what step you're up to, where you need help and what's going wrong if anything.