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

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.