Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I'm creating a financial report where we need to see the actuals per quarter and using a forcasted quarter (A measure I used to pull the prior quarter total
What I'm trying to do is sum the first 3 quarters with the 4th quarter forecasted value as a total. The old excel report just did a sum of the 3 quarters plus the forecasted value (that was just was copy of the prior quarter). But I'm confused on how to go about this in DAX. The current total in PBI is based upon all 4 quarter ytd (That is not going to work).
Excel Version:
Power BI Version
Solved! Go to Solution.
So for now I've come up with a work around but sadly this wont work dynamically. But it is giving me the values I need for the report, I'll just need to update the measures for each quarter.
• First I did a sum with hard dates for the first 3 quarters:
Hi @Anonymous ,
Could you please share some sample data to me if you don't have any Confidential Information?
I'm not quiet sure about your table structure, some sample data might make me more effective in helping you.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Not sure how I can show sample data seeing as this is part of a much bigger report. Plus I have about 20 measures. That and this is all pretty confidential info. The matrix table I showed has two rows (Department and Dept Manager) columns are dates.quarter and values have 8 different fields (But I'm only showing Actuals and the forecast measure) I had to do this to show all departments even if they didn't have a budget or anything spent the year they are researching.
But to simplify this a bit more what you are seeing is the value portion of the matrix table (Had to hide the rows due to confidentiality) The "CY" values come from the Actuals Field, the forecasted value is from a PreviousQuarter measure. I just need to sum these to create a total.
So for now I've come up with a work around but sadly this wont work dynamically. But it is giving me the values I need for the report, I'll just need to update the measures for each quarter.
• First I did a sum with hard dates for the first 3 quarters:
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |