Calculation to Compare Today() and Previous Day

[Nanakwame]

Hi Power BI Experts,

I am looking to calculate "Current Position" for today and yesterday. 

Formula for current position = Opening Balance + Debits + Debits (shown as a negative value) + Outstanding Checks (Shown as a negative Value)

So far i have the measures below and i get an error/blank value. 

Yesterday's Current Position = CALCULATE([sum of Current Position], FILTER('Date', 'Date'[Date] = TODAY()))

Today's Current Position = CALCULATE([Current Position], dailyKPI[Today])

I used the date table and also the date from the fact table and both didnt work. 

[v-yanjiang-msft · Accepted solution]

Hi @Nanakwame ,

According to your description, I create a sample, is this your expected outcome?

In this formula, date in the filter can also be the fact table or calendar table, if you use the calendar table , the two tables should make relationship first.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nanakwame]

Hi @v-yanjiang-msft 

I get an error for this function. Please see image below. "Sum of Current Position" is measure which is a sum of columns in the table.

[Gabriel_Walkman]

Maybe the sum of current positions needs to be a column instead?

[Nanakwame]

Hi @v-yanjiang-msft 

I created it as a column and it worked however i dont thing the values for "sum of current position" as a column is right 

[v-yanjiang-msft]

Hi @Nanakwame ,

If "Sum of Current Position" is a measure, you should remove MAX function, like this:

Today's Current Position = CALCULATE([Measure],FILTER(ALL('Calendar'),'Calendar'[Date]=TODAY()))

Best Regards,

Community Support Team_kalyj

[Gabriel_Walkman]

If you just use [sum of Current Position] in a visual, does it work?

What is the error you're getting?

The column named [Today] seems off. Have you made a calculated column just for displaying which rows are today? Scratch that, should work with the 'Date'[Date] = today() method, if your relationships from the calendar table are correct.

[Nanakwame]

Hi @Gabriel_Walkman 

"sum of current position" is a measure that sums up a few columns in the table. 

[Nanakwame]

Hi @Gabriel_Walkman 

It comes as "Blank"