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 ,

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-date/m-p/140882

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.