Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
Please find solution at
https://www.dropbox.com/s/8wqtz6cfintxpa2/DateBasedonLastDate.pbix?dl=0
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 |
Please find solution at
https://www.dropbox.com/s/8wqtz6cfintxpa2/DateBasedonLastDate.pbix?dl=0
Thank you!
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.
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.
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
" 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.
Help when you know. Ask when you don't!
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |