Power BI Trying to Subtract Last Day of Month

[mdelg]

I am working in Power BI and need to subract the last day value of the previous month (value on 4/30) to each day in the following month. So, value of 4/30 minus 5/1 value, 5/2 value etc. Here is a screenshot of the data needed. 

Date                          Type            Dollars      Previous Month Chg

4/30/2021                Blue               400.00

4/30/2021                Green             400.00

4/30/2021                Yellow            400.00

5/1/2021               Blue                   600.00       XXX - need this to show as (200)

5/1/2021               Green                700.00       XXX - need this to show as (300)

5/1/2021              Yellow                800.00       XXX - need this to show as (400)

and that formula continues for the month of May for each type and subtracting from 4/30 data. I currently have this in one table in PBI with a month filter so it only shows May data right now but needs that other column with the calculation. I have tried to do it with a measure but haven't had luck. Any help appreciated! 

[Jihwan_Kim · Accepted solution]

Hi, @mdelg 

Please check the below picture and the sample pbix file's link down below.

I tried to create a sample based on the explanation.

All measures are in the sample pbix file.

Dollars Total =

SUM(Data[Dollars])

Dollars total lastdate P.M =

VAR lastdatepreviousmonth =

CALCULATE (

LASTNONBLANK ( Data[Date], [Dollars Total] ),

FILTER ( ALL ( Dates ), Dates[Date] <= EOMONTH ( MAX ( Dates[Date] ), -1 ) )

)

RETURN

IF (

NOT ISBLANK ( [Dollars Total] ),

IF (

ISFILTERED ( Dates ),

CALCULATE ( [Dollars Total], Dates[Date] = lastdatepreviousmonth )

)

)

Variance VS lastdate P.M =

IF (

ISFILTERED ( Dates ) && NOT ISBLANK ( [Dollars total lastdate P.M] ),

[Dollars Total] - [Dollars total lastdate P.M]

)

https://www.dropbox.com/s/pa2qb5l87gibod9/mdelg.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[mdelg]

Any idea how to get this data on the same file I had attached to only refresh the last week instead of all the data? From what I can read it's called incremental refresh but unsure how to add this. My file sources disappear after a month so I don't want those to go away. Thanks! 

[Jihwan_Kim]

Hi, @mdelg 

Is the model import mode?

Then you can create parameters in Power Query Editor.

And set up the incremental refresh in the power bi report page by right-clicking the table.

When you right click the table, you can see the option for an incremental refresh. In here, I think you can select the option for something like recent 5 years.... 

[mdelg]

I believe so, I just tired to create parameters. I did start range 5/1/2021 and end range 06/30/2021. When I went back out to the query, it has an error, "We cannot convert a value of type Binary to type DateTime." 

Another issue that might be causing is the source data enventually disspears from the file. That was the reason for wanting to set up incremental refresh since I only want a week or so to refresh and the rest of the data to stay put. Any ideas are welcome! 🙂 

[Jihwan_Kim · Accepted solution]

Hi, @mdelg 

Please check the below picture and the sample pbix file's link down below.

I tried to create a sample based on the explanation.

All measures are in the sample pbix file.

Dollars Total =

SUM(Data[Dollars])

Dollars total lastdate P.M =

VAR lastdatepreviousmonth =

CALCULATE (

LASTNONBLANK ( Data[Date], [Dollars Total] ),

FILTER ( ALL ( Dates ), Dates[Date] <= EOMONTH ( MAX ( Dates[Date] ), -1 ) )

)

RETURN

IF (

NOT ISBLANK ( [Dollars Total] ),

IF (

ISFILTERED ( Dates ),

CALCULATE ( [Dollars Total], Dates[Date] = lastdatepreviousmonth )

)

)

Variance VS lastdate P.M =

IF (

ISFILTERED ( Dates ) && NOT ISBLANK ( [Dollars total lastdate P.M] ),

[Dollars Total] - [Dollars total lastdate P.M]

)

https://www.dropbox.com/s/pa2qb5l87gibod9/mdelg.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[mdelg]

Here is the file I am working with in. I tried to copy the measure but it wasn't working. 

Essentially I need a column next to A1, Greenware and Refire that does the calulation of last day of previous month minus each day, so 4/30 for A1 minus 5/1 A1 data and so on. I'll then be doing it for both tables which are pieces and dollars of the data. Appreciate the help! 

https://www.dropbox.com/s/4v89yasd4dsv6y4/WIP%20V4%20Final.pbix?dl=0

[Jihwan_Kim · Accepted solution]

Hi, @mdelg 

Thank you for your feedback.

I suggest having a dim-date table like shown in the sample pbix file's link down below.

Please check the link below, and all measures are in the file.

https://www.dropbox.com/s/uz2cvgf7hwy2n8e/WIP%20V4%20Final.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[mdelg]

Amazing! That is awesome, thanks for help!