cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

## 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

@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

@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

@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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors