cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Power BI column calculation for Next Day amount

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.

1 ACCEPTED SOLUTION
Microsoft Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Microsoft Employee

@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])))

https://community.powerbi.com/t5/Desktop/DAX-Calculation-Total-for-Previous-Work-Day-regardless-of-d...

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Microsoft Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Perfect! This worked wonderfully. I appreciate it.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.