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.
Share on Google Plus

About Tom DeMeulenaere

Highly accomplished information technology professional with extensive knowledge in System Center Configuration Manager, Windows Server, SharePoint, and Office 365.
    Blogger Comment

0 comments:

Post a Comment