Power BI column calculation for Next Day amount

[nchambe]

My scenario is I have a SharePoint list being pulled into Power BI that gives a gate/visitor count for each day for multiple gates.

I need one calculated column that basically brings in the next day's opening count as today's closing count (for each gate)(ClosingCountGate1=NextDayOpeningCountGate1). Then I'll be able to calculate each day's total (Closing 400,000 opening 375,000 and total 25,000 for example) and create reports based on YTD, growth, most popular gates and days of the week, etc.

Does anyone know of a way using DAX or otherwise to do that? I have a date table, which seems to be useful for these sorts of things. And I've been toying with NEXTDAY but haven't hit it yet.

[v-yuezhe-msft]

@nchambe ,

Add a column using the DAX below in your date table, make sure your date table have a integer column which is formatted as YYYYMMDD .

Previous day = CALCULATE(FIRSTNONBLANK('Date'[DateInt],""),'Date'[DateKey]=EARLIER('Date'[DateKey])-1)

Then create a measure in your table using DAX below.

NextDayOpeningCountGate = CALCULATE(SUM(table[closingcountgate]),FILTER(ALL('Date'),'Date'[DateInt]=MAX('Date'[Previous day])))

There is a similar thread for your reference, if  you have any questions about the DAX, please share sample data of your table.
https://community.powerbi.com/t5/Desktop/DAX-Calculation-Total-for-Previous-Work-Day-regardless-of-d...

Regards,
Lydia

[nchambe]

@v-yuezhe-msft,

I thought this worked, but then upon looking a bit closer noticed it wasn't working. I've spent hours today trying to figure out what needed tweaked with still no luck. The statement doesn't give me an error, but the column is blank.

Scenario: I need to pull TODAY's "Opening Count" (what an entry gate counter is currently at) into YESTERDAY's (or most recent day with data) "End Count" column as seen here. A bonus would be calculating the difference between the opening and ending as seen on the far right column, but I can't get there without the end count pulling in correctly.

I tried:

EndCount = CALCULATE(SUM('Gate Count'[OpeningCount]),FILTER(ALL('Date'),'Date'[DateInt]=MAX('Date'[Previous day])))

with my date table having the following previous day column:

Previous day = (CALCULATE(FIRSTNONBLANK('Date'[DateInt],""),'Date'[Date]=EARLIER('Date'[Date])-1))

and the following DateInt column:

DateInt = format('Date'[Date],"YYYYMMDD")

Both "Previous day" and "DateInt" in my date table are integers. "OpeningCount" from my gate count table is an integer. "Date" in both tables is, of course, a date column.

[v-yuezhe-msft · Accepted solution]

@nchambe,

In your scenario, you don’t need to involve Date table, just create the following columns in the Gate Count table.

End Count =

var previous= CALCULATE(FIRSTNONBLANK('Gate Count'[OpeningCount],""),FILTER('Gate Count','Gate Count'[Gate]=EARLIER('Gate Count'[Gate])&& 'Gate Count'[Date]>EARLIER('Gate Count'[Date])))

return

IF(ISBLANK(previous),BLANK(), previous)

Change = IF(ISBLANK('Gate Count'[End Count]),BLANK(),'Gate Count'[End Count]-'Gate Count'[OpeningCount])



Regards,
Lydia

[nchambe]

Perfect! This worked wonderfully. I appreciate it.