Dynamic yesterday until specific time and today date

[Anonymous]

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 use this in a measure to sum up sales and a text to show in the dashboard which day is showing up.

Any ideas if that is possible?

Cheers,

[v-janeyg-msft · Accepted solution]

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]

@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]

Hi @AllisonKennedy

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

[AllisonKennedy]

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