Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AndresSalomon
Helper II
Helper II

Execute a mesure based on a condition

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

1 ACCEPTED SOLUTION

@AndresSalomon

 

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!

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

@AndresSalomon

 

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!

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






v-ljerr-msft
Employee
Employee

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. 

 

t1.PNG

 

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. Smiley Happy

 

c1.PNG

 

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 Smiley Sad

 

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:

 

https://community.powerbi.com/t5/Desktop/Perform-a-measure-when-a-filter-is-active/m-p/300999#M13293...


Hoping to hear from you, I'm still working and looking for a solution. Thanks again!!

 

Kind regards,

Andy.-

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.