Two date calculations I can't find info on

[mjohnsonsmi]

Hello all,

I have data that can come in with a 30-45 day delay. I'm trying to set up a report that'll do the below, with that delay in mind. I can't find any examples that apply to my specific scenerio so here's what I'm trying to do.

Scenerio:

I run this report on April 1, the last date in the data is 2/23/2020. I want to get the sum of table SALES, column UNITS from 2/1/2020 - 2/23-2020. Taking the date of the last available record and the first date of that same month.

Then replicate the above but for the previous month so it's apples to apples. Showing me sales from 1/1/2020 - 1/23/2020.

Thoughts?

[mjohnsonsmi]

Thanks everyone for chiming in! Sample table is below, pretty vanilla setup.

Using the below example, this would get the sum of 1/14/2020 through 1/20/2020 but ignore the 1/27/2020 record. Only because the highest date in Feburary is 2/23/2020. Assume this report is getting run in April via automatic email subscription so no human interaction with selection fields is a requirement.

CSV download of the below https://drive.google.com/open?id=1vCUgb3879_JvXP69kCfoxG03UDMn_AhT 

Date	Units	Model	FName	LName	Company
1/14/2020	1	Widget 1	Clark	Kent	Justice League
1/20/2020	1	Widget 3	Lex	Luthor	Legion of Doom
1/27/2020	1	Widget 1	Peter	Parker	SHIELD
2/2/2020	1	Widget 2	Bruce	Wayne	Wayne Enterprises
2/11/2020	1	Widget 1	Bruce	Banner	Avengers
2/17/2020	1	Widget 1	Tony	Stark	Avengers
2/23/2020	1	Widget 3	Black	Widow	Avengers

[amitchandak · Accepted solution]

@mjohnsonsmi 

Please find solution at

https://www.dropbox.com/s/8wqtz6cfintxpa2/DateBasedonLastDate.pbix?dl=0

[mjohnsonsmi]

Thank you!

[v-yingjl]

Hi @mjohnsonsmi ,

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Yingjie Li

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

[Tad17]

Hey @mjohnsonsmi 

Have you thought about using a timeline slicer and the PARALLELPERIOD or more likely the DATEADD functions? Check out the thread in the link below as I believe it answers your problem: https://community.powerbi.com/t5/Desktop/Compare-data-with-previous-period-last-month/td-p/90426

If this helps please kudo.

If this solves your problem please accept it as a solution.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[kentyler]

" I want to get the sum of table SALES, column UNITS from 2/1/2020 - 2/23-2020. "

You should be able to write a measure that gets the MIN() date in the table.

And from that date you can get the MONTH()

that should let you filter for the date range you want.

Once you know the current month you can use datediff to get the previous month (in case you're in Jan and it won't work to subtract 1). and then filter for the records in the previous month.

If you want help with the measure, post some sample data or, better yet, a sample power bi file and I'll write an example for you.