Forum Discussion
Execute a mesure based on a condition
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!
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. :smileyhappy:
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 :smileysad:
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.-
