Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
For the current year (CY), I got my cumulative total measure to work for selected time periods with showing blanks for futrue periods (shown below).
Next I want to produce something similar for the prior year (PY), but with the PY calendar adjusted to align by day of the week (eg Wed to Wed)
Basically I want to take the CY date and subtract 364 days (This is not a perfect calculation when you incorporate leap year and a 53rd week, but works for this project)... Can't quite get it to work.
Hoping the community can help me out,
Thanks!
Cumulative New Pawns $ =
VAR LastPawnDate = CALCULATE( LASTDATE(FactPawn_Rollforward[close_date] ), ALL(FactPawn_Rollforward) )
RETURN
IF(SELECTEDVALUE( DimDate[Date] ) > LastPawnDate, BLANK(),
CALCULATE( [New Pawns $] ,
FILTER( ALLSELECTED( DimDate),
DimDate[Date] <= MAX( DimDate[Date] ) )))
Solved! Go to Solution.
Hi,
This measure works
Measure = CALCULATE([Cumulative New Sales $ PY],DATESBETWEEN(DimDate[Date],MINX(ALLSELECTED(DimDate[Date]),DimDate[Date]),MAX(DimDate[Date])))
Hope this helps.
@1Kash_PBI - I'm not following your code very well. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, 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
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Thanks for the reply!
Unfortunately, the results are not a cumulative or running total by day ,like my current year code does.
Thanks for the reply and apologies for not reading the wiki beforehand... Let me try again.
The code I posted was for Current Year
What I need is for Prior Year
How to get from Point A to Point B: Noob explanation
I hope this makes more sense... Thanks again for looking over.
Current Year Dates
Hi,
Try this measure
=calculate([your measure],datesbetween(calendar[date],min(calendar[date])-364,max(calendar[date])-364))
Ensure that to your visual/slicers, you drag year and month from the calendar table.
Hope this helps.
Also, my visual is showing by day, if that helps...
Hi,
Share some data and show the expected result.
Thanks again!
In the below table I've provided prior year sales by day as well as the expected cumulative results...
Reminder Prior year is defined as current years date less 364 days)
Also, I provided a link to some sample data that includes the visual with current year cumulative results performing as expected.
Date | Cumulative New Sales $ PY | By Day New Sales $ PY |
3/1/2020 | - | - |
3/2/2020 | 3,715 | 3,715 |
3/3/2020 | 4,840 | 1,125 |
3/4/2020 | 5,615 | 775 |
3/5/2020 | 7,310 | 1,695 |
3/6/2020 | 7,390 | 80 |
3/7/2020 | 7,750 | 360 |
3/8/2020 | 7,750 | - |
3/9/2020 | 8,670 | 920 |
Hi,
This measure works
Measure = CALCULATE([Cumulative New Sales $ PY],DATESBETWEEN(DimDate[Date],MINX(ALLSELECTED(DimDate[Date]),DimDate[Date]),MAX(DimDate[Date])))
Hope this helps.
Thank you sir! I'm beyond thankful!!
As a new member, I truly appreciate you taking time to help me.
You rock!
You are welcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |