Today Filter in Reports

[successken]

I'm not if I'm missing this. Is it possible to create gauge reports and such with a date filter of Today or Yesterday without utilizing the Q&A?

My goal is to have a quick dashboard reference on yesterday's sales performance.

[Elliott]

Following on from MrJolly's post, and thanks to smoupre for providing this solution to me orginally..

DATEDIFF can be acheived with the following formula:

Days Aging = DATEDIFF([Date],TODAY(),DAY)

This will give you a new column which will specify the total number of days from the current day, starting from '0' as today.. '1' as yesterday etc.

This gives better functionality in the case that you may want to specify different/longer time periods.

[successken]

MrJolly and Elliot - thank you for your replies. Your prespective with DATEDIFF has added differnt dynamic that I look forward to try out. I am going to see if I can add this via Power Query.

[fbrossard]

If you have a "date" table, you could enrich it with some calculated column.

For example :

[Relative Date Offset] = INT([Date] - TODAY()) [Relative Date] = IF([Relative Date Offset]=0; "J "; "J " & IF([Relative Date Offset]>0; "+"; "") & [Relative Date Offset]) 
[Relative Month Offset] = ((12 * YEAR([Date])) + MONTH([Date])) - ((12 * YEAR(TODAY())) + MONTH(TODAY())) 
[Relative Month] = IF([Relative Month Offset]=0; "M "; "M " & IF([Relative Month Offset]>0; "+"; "") & [Relative Month Offset]) 
[Relative Week Offset] = INT(([Date] - (TODAY() - WEEKDAY(TODAY();2)+1))/7) 
[Relative Week] = IF([Relative Week Offset]=0; "W "; "W " & IF([Relative Week Offset]>0; "+"; "") & [Relative Week Offset])

Bla bla bla ...

You can modify this for your needs. And use them as filter, slicer or just axis in your reports.

[karthik]

Please note that we have had few issues using Today or Now in the Power BI Service as it would return the current date based on the regional/date settings of the data centre where the service is hosted. If you are in a different time zone this would be an issue.

[elliotdixon]

Hi @karthik have you heard of any updates / fixes around this? Still causing me lots of issues being in New Zealand and +13 hours.

ED

[karthik]

@elliotdixon

The workaround is to use NOW()+13/24 and convert this to date datatype to get to the NZ timezone in the service. This is not ideal as this would change depending on day light saving settings per region which is quite soon in NZ (I am based in NZ as well 🙂 )

The workaround to make it dynamic is to create a daylight savings tables with the start and end date and use with the time difference related to the date range.

Do you use SQL Server (or any relational database) as a data source in your app? If so you can create a view on your date dimension and create a field named "Is Current Date" set to 1 for current date. You can then use this attribute "Is Current Date" in your calculations.

[elliotdixon]

Cheers @karthik
I saw another post on the forums - TimeZone Fix and managed to apply that after messing around for quite a while.

@ashishrj recommended

New_Calculated_Date = Old_Calculated_Date + (13/24) {changed for NZ}

Your idea about a daylight savings table is a good one otherwise I am going to have heaps of datasets to fix up in April. Guess it just means a bigger calcuation for the dates as well.

Quite annoying that there is so much work required to get the data to match our timezone!

I do use a RelativeDate column to give days previous and infront of today. Very useful indeed.

RelativeDate = [Date]-TODAY()

 
On another note.
As a fellow Kiwi have you managed to work out how to get the dates converted to non US setup once uploaded to PowerBI.com.

on my desktop app the dates look like

but when uploaded they automatically change to

Would be great to fix this.

Rgds - ED

[Phil_Seamark]

Just type in "tomorrow" instead 😉

(also in NZ)

[elliotdixon]

@Phil_Seamark if only it was that easy

[austinsense]

You could create calculated columns on the date table ...

TODAY FLAG = IF( Date[Date] = NOW(),1,0)
YESTERDAY FLAG = IF( Date[Date] = NOW()-1,1,0)

and then use those as filters on the page or visualization.

[successken]

Thank you. I took your idea and applied it in Power Query with 

if Date.IsInCurrentDay([Date]) then "Today"

else if Date.IsInPreviousDay([Date]) then "Yesterday"

else ""

[Phil_Seamark]

A [Days from Today] column in a Date Dimension works especially well in Power BI.  You can DATEDIFF against today to allow users to filter on a rolling range of dates.