Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all, hope you are doing good 🙂 Looking for some help here.
My Fiscal Year starts in Sep to Aug 31st.
I have two fact tables: tblForecast and tblActuals. They look like:
tblForecast
Project______Category________PNP__________Month_______ForecastValue
1______________Cat1__________Payroll__________Sep____________100
2______________Cat1_________NonPayroll_______Oct____________100
2______________Cat3____________Payroll_________Nov____________100
3______________Cat2____________Payroll_________Dec____________100
3______________Cat5__________NonPayroll_______Jan____________120
3______________Cat6____________Payroll_________Feb____________104
4______________Cat2____________Payroll_________Mar____________105
And so on with all the forecast of all the projects, all the months and all the categories, no matter they are prior months. A long table.
tblActuals
Project______Category________PNP__________Month_______ActualValue
1______________Cat1__________Payroll__________Sep____________150
2______________Cat1_________NonPayroll_______Oct____________160
2______________Cat3____________Payroll_________Nov____________10
3______________Cat2____________Payroll_________Dec____________120
3______________Cat5__________NonPayroll_______Sep____________120
3______________Cat6____________Payroll_________Oct____________114
4______________Cat2____________Payroll_________Dec____________165
Is pretty the same, with the big difference it only has values for the prior months that already have actuals (only for Sep, Oct, Nov and Dec).
And then, the dimension tables: dimMonth, dimPNP, dimProject.
I created two measures to compute the actuals value and forecast value.
Actuals YTD = SUM ( tblActuals[ActualsValue] )
Forecast = SUM ( tlbForecast[ForecastValue] )
I need to create a PivotTable (same a Matrix) where I need to show for the months that have actuals, the actuals measure, and the forecast measure to the rest.
The goal should be something like this:
Project______PNP__________Sep______________Oct___________Nov____________Dec___________Jan_________Feb________Mar.... so on
1__________Payroll_____[Actuals YTD]___[Actuals YTD]__[Actuals YTD]__[Actuals YTD]__[Forecast]__[Forecast]__[Forecast]______
1_______NonPayroll_____[Actuals YTD]___[Actuals YTD]__[Actuals YTD]__[Actuals YTD]__[Forecast]__[Forecast]__[Forecast]______
2__________Payroll_____[Actuals YTD]___[Actuals YTD]__[Actuals YTD]__[Actuals YTD]__[Forecast]__[Forecast]__[Forecast]______
2_______NonPayroll_____[Actuals YTD]___[Actuals YTD]__[Actuals YTD]__[Actuals YTD]__[Forecast]__[Forecast]__[Forecast]______
etc.
Instead segmenting by PNP, we can use the Category, doesn't matter.
Is there a way to create a GlobalMeasure that computes one or another measure depending the month in the same PivotTable (Matrix)?
I thought that this may help to the solution, but I'm stucked. I create a column in the dimMonth table:
Month___________Actuals or Forecast
Sep_______________________A
Oct_______________________A
Nov_______________________A
Dec_______________________A
Jan_______________________F
Feb_______________________F
Mar_______________________F
Apr_______________________F
else with the rest of the months. Only the first 4 have actuals.
The measure of that column is based on the tblActuals, that only have the month with Actuals values.
=IF( COUNTX( RELATEDTABLE(tblActuals), tblActuals[Month] ) > 0, "A", "F")
That column may help to create the GlobalMeasure logic. But, truly, I don't know how to move on.
Any insight would be helpful!!
Please let me know if you need further explication. Thank you very much in advance.
Kind regards,
Andy
Solved! Go to Solution.
I have the same model as you have. Assuming both forecast and actual table are related to the same period table ('date') I use a measure which is basically an IF function:
Monthly Measure = IF(MAX(date[year]) = YEAR(TODAY()) -1, [ACTUALS], IF(MAX(date[month])<MONTH(TODAY()) && MAX(date[year]) = YEAR(TODAY()), [ACTUALS]], [FORECAST]))
This should give you the monthly data. To get the totals for quarter and year, you need to create a new measure using SUMX(SUMMARIZE('date', 'date[month]), [monthly measure]).
Hope that helps,
Paul.
EDIT : you might have to play around with the statement regarding last year's months; my model is based on a calendar year so it is pretty straightforward.
EDIT #2: I've just realised that the date columns in the IF function need to be wrapped with the MAX function for the measure to work. Sorry about that oversight!
Proud to be a Super User!
Paul on Linkedin.
OK, here is what I am thinking on this one. Create a measure like this:
AorF = VAR actual = LOOKUPVALUE(tblActuals[ActualValue],tblActuals[Project],MAX(tblForecast[Project]),tblActuals[Category],MAX(tblForecast[Category]),tblActuals[PNP],MAX(tblForecast[PNP]),tblActuals[Month],MAX(tblForecast[Month])) RETURN IF(ISBLANK(actual),MAX(tblForecast[ForecastValue]),actual)
If you put this measure into a table visualization along with Project, Category, PNP and Month from your tblForecast, I believe you will get what you are looking to achieve.
Also, my 2 tables are NOT related.
Hi @Greg_Deckler! Thanks for watching this post. Your formula worked for you? It's giving me strange values...
I have a Data Model. I mean, the corresponding Look-Up tables (ProjectID, PRNPR, Category) that relate both Data tables (tblActuals, tblForecast). I'm saying because you said that you don't have related tables, and I do. Maybe that is causing this problem.
I have the same model as you have. Assuming both forecast and actual table are related to the same period table ('date') I use a measure which is basically an IF function:
Monthly Measure = IF(MAX(date[year]) = YEAR(TODAY()) -1, [ACTUALS], IF(MAX(date[month])<MONTH(TODAY()) && MAX(date[year]) = YEAR(TODAY()), [ACTUALS]], [FORECAST]))
This should give you the monthly data. To get the totals for quarter and year, you need to create a new measure using SUMX(SUMMARIZE('date', 'date[month]), [monthly measure]).
Hope that helps,
Paul.
EDIT : you might have to play around with the statement regarding last year's months; my model is based on a calendar year so it is pretty straightforward.
EDIT #2: I've just realised that the date columns in the IF function need to be wrapped with the MAX function for the measure to work. Sorry about that oversight!
Proud to be a Super User!
Paul on Linkedin.
Hi @AndresSalomon,
Is pretty the same, with the big difference it only has values for the prior months that already have actuals (only for Sep, Oct, Nov and Dec).
In this scenario, I would suggest you merge tblForecast and tblActuals into a single table use Merge Query option in Query Editor. The merged table will be looks like below. For more details about how to use Merge Query feature, your can refer to this article.
Then you can simply create a new calculate column in the merged table to get the actual value and forecast value accordingly and show it on your report.
Regards
Hi @v-ljerr-msft, hope you are doing good. Thank you very much for your response.
I loved your reply, never thought about merging the queries... But is not going to work, unfortunately
Why? Because the tblForecast and tblActuals are similar, but not exactly similar. I tried to represent them here in a very VERY summarized way, but they are a bit more complex. It is not the perfect scenario...
They have similar records and fields, but the categories doesn't match exactly in one table and another. Some of them yes, but not everyone. That's why merging the queries will create a merged table with records out of phase and the column you created won't work.
Sorry for this... I never thought about merging. I think that a DAX measure should help me here, using the column I created in Month table. Something like a conditional that asked if there is "A" give me this measure, else give the other one, but playing with the contexts because I need them in the same table.
I created a post here a time ago asking for something similar of what I need now. I'm thinking that the solution I found there could help with this new query. Maybe we can adapt it to the needs of this post. If you have time, please take a look:
Hoping to hear from you, I'm still working and looking for a solution. Thanks again!!
Kind regards,
Andy.-
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |