Wednesday, July 3, 2019

Power BI - Column for data from previous date

In Power BI, I needed to add a column for data from a previous date, not Today()-1. Since this was pulling data from a directory that might not have data exported in CSV format to it regularly, I needed to look for the past date, and then get the data from that.

The first thing I needed to do was get the date of the past report, and pull it into a column. So I opened up the modeling tab, and put a new column into my fields using a query similar to this one:
Report Yesterday Date = CALCULATE(MAX('Data'[Report Date]), FILTER('Data', 'Data'[Report Date] < EARLIER('Data'[Report Date])))

This created a column with the previous date in it.
Then I created another column with the data from the previous day in it
Yesterday Data = CALCULATE( SUM('Data'[Devices]), FILTER('Data', 'Data'[Report Date] >= EARLIER('Data'[Report Yesterday Date]) &&'Data'[Report Yesterday Date]<EARLIER('Data'[Report Yesterday Date]) &&'Data'[Key Name]=EARLIER('Data'[Key Name])) )

After adding these two columns I get results like this:

From there I can create my visuals based off of filtered dates, and generate my dashboard.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.