Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
My team takes on customer projects throughout the year and I'd like to see how their sales change while working with us. Each project has it's own start date and I'd like to see sales for the 12 months leading up to that start date, and sales from the 12 months after the start date. I have those formulas below and they seem to be working. For projects that are less than 12 months old, the '12M Sales Since Start' formula works fine as a YTD calculation, but I'm having trouble using SAMEPERIODLASTYEAR or any other command to find the Previous YTD for each project that is less than a year old.
12M Sales Up To Start =
Solved! Go to Solution.
Hi @JBGyro ,
Try this.
Measure 2 =
VAR _a = MIN('Projects'[StartDate])
VAR _c = SELECTEDVALUE('Projects'[Name])
VAR _b = CALCULATE(SUM('Sales'[Amount]),'Date'[Date] >= _a - 365 && 'Date'[Date] <= TODAY(),ALL('Projects'[StartDate]))
RETURN IF(ISBLANK(_c),BLANK(),_b)
Hi @Greg_Deckler , @MNedix ,thanks for the quick replies, I'll add further.
Hi @JBGyro ,
The Table data is shown below:
Please follow these steps:
1. Use the following DAX expression to create a measure
Measure =
CALCULATE(SUM('Sales'[Amount]),'Date'[Date] >= MIN('Projects'[StartDate]) - 365 && 'Date'[Date] <= MIN('Projects'[StartDate]),ALL('Projects'[StartDate]))
2.Final output
Thank you for this very detailed response! It looks like this is capturing the full year of sales leading up to the project start date, which is what I already have. I'm looking to mimic a YTD view using the project start date. So if a project started on March 1, 2024 and today is May 8, 2024, I would like to see sales from March 1, 2023 to May 8, 2023. Is this possible?
Hi @JBGyro ,
Try this.
Measure 2 =
VAR _a = MIN('Projects'[StartDate])
VAR _c = SELECTEDVALUE('Projects'[Name])
VAR _b = CALCULATE(SUM('Sales'[Amount]),'Date'[Date] >= _a - 365 && 'Date'[Date] <= TODAY(),ALL('Projects'[StartDate]))
RETURN IF(ISBLANK(_c),BLANK(),_b)
@JBGyro Better Year to Date Total - Microsoft Fabric Community
Hi,
Have you tried DATEADD? For example, since you already have a measure for 12M Sales Since Start then you can try:
12M SSS Last Year = CALCULATE([12M Sales Since Start],DATEADD('Date'[Date],-1,YEAR))
Thank you for the reply. I have tried this and it isn't pulling in accurate values, and for some reason it's not pulling any data for projects started this year.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |