topic Re: Power BI Trying to Subtract Last Day of Month in DAX Commands and Tips

Power BI Trying to Subtract Last Day of Month

mdelg — Wed, 19 May 2021 04:52:37 GMT

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!

Re: Power BI Trying to Subtract Last Day of Month

Jihwan_Kim — Wed, 19 May 2021 06:09:19 GMT

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

Re: Power BI Trying to Subtract Last Day of Month

mdelg — Wed, 19 May 2021 21:08:10 GMT

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

Re: Power BI Trying to Subtract Last Day of Month

Jihwan_Kim — Thu, 20 May 2021 03:28:19 GMT

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

Re: Power BI Trying to Subtract Last Day of Month

mdelg — Thu, 20 May 2021 04:36:08 GMT

Amazing! That is awesome, thanks for help!

Re: Power BI Trying to Subtract Last Day of Month

mdelg — Mon, 24 May 2021 04:39:04 GMT

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!

Re: Power BI Trying to Subtract Last Day of Month

Jihwan_Kim — Mon, 24 May 2021 05:00:31 GMT

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....

Re: Power BI Trying to Subtract Last Day of Month

mdelg — Wed, 26 May 2021 02:51:37 GMT

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! 🙂