autoincremnt date to previous date

[aka]

hey guys,

I want to autoincrement my full-date filter in my daily order report. i.e. my date filter should change everyday to the previous date

for example if today is 4/14/2017 then my date filter in the report should autoincremnt to 4/13/2017

How can i achive this?

Please help me with the DAX formula... 

[v-yulgu-msft]

Hi @aka,

In your scenario, did you use a slicer to fiter data? Based on my understanding, you want the date value selection in slicer to be dynamically changed everyday. That is to say, each day we open the report, the max date displayed in slicer is the date of yestoday, right? If so, please refer to @VP's advice.

If your requirement is to refresh data automatically everyday so that the report always displays data records till yestoday, this cannot be achieved by DAX. However, you can try to add a date filter in Power Query so that the imported data can be dynamically changed.

Regards,
Yuliana Gu

[aka]

hi @v-yulgu-msft,

is there any other approch without using dax or creating any filters usiong DAX?

[dedelman_clng]

Hi @aka

I have a report that when I refresh only calculates the date table up to the current date.  You can adapt this code in Query Editor -> Advanced Editor to your needs

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <= Date.From(DateTime.LocalNow()))

• Change "#Changed Type" to whatever the "name" of your last step is in Query Editor. 
• Remove the = from <= and it should build your date table up to only the previous day. 
• No visual filters or DAX changes necessary.

Hope this helps.

David

[aka]

@dedelman_clng 

Hi, where should i applly this, can you please explain me in detail.

I will be very gratefull.

Thanks

[dedelman_clng]

Hi @aka

Open Query Editor

Find the query for the date table in your model

Take a note of the last step name (in my case Changed Type)

Click on Advanced Editor

In the code, go to the last line and insert the code snippet I gave earlier

Replace "#Changed Type" to the name of the last step; remove the = from <= 

Name your new step and also put it after the "in" (in my case "#Filtered Rows".  The editor should show if you have any syntax errors - correct those and hit "OK"

This should give you a date table that only goes up to yesterday.

[aka]

@dedelman_clng  there were no syntax error but am getting this error do you have any idea.

Thanks

[dedelman_clng]

change "each [Date]" to "each [FullDate]"

[aka]

@dedelman_clng that worked but got anothe error

[aka]

@dedelman_clng

Any comments on the previous error?

[dedelman_clng]

This may be a limitation based on your data source.  My date table was constructed from a list, not pulled from a database.  Others may need to answer how it can be done on a SQL-based date table.

@MarcelBeug is an M specialist and may be able to get you the rest of the way.

[aka]

@dedelman_clng

Allrite thanks fot your help 🙂

[MarcelBeug]

Your FullDate is a datetime field. If you want to compare with date, then use Date.From([FullDate])

[aka]

@MarcelBeug

can you explain please?

[MarcelBeug]

Adjust the code from @dedelman_clng to:

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.From([FullDate]) < Date.From(DateTime.LocalNow()))

[VP]

If you are adding new column you can use

New Date = Table1[Old Date].[Date]-1

And use New column as filter

There are other options based on Where you are using filter.