Forum Discussion

nchambe's avatar
nchambe
Icon for Advocate II rankAdvocate II
9 years ago
Solved

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 ...
  • Anonymous's avatar
    Anonymous
    9 years ago

    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's avatar
nchambe
Icon for Advocate II rankAdvocate II
9 years ago

Anonymous,

 

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.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    nchambe
    Icon for Advocate II rankAdvocate II
    9 years ago

    Perfect! This worked wonderfully. I appreciate it.