cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AndresSalomon Regular Visitor
Regular Visitor

Execute a mesure based on a condition

Hi all, hope you are doing good Smiley Happy 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

Accepted Solutions
PaulDBrown Senior Member
Senior Member

Re: Execute a mesure based on a condition

@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!

 

View solution in original post

5 REPLIES 5
v-ljerr-msft Super Contributor
Super Contributor

Re: Execute a mesure based on a condition

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

AndresSalomon Regular Visitor
Regular Visitor

Re: Execute a mesure based on a condition

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

Super User
Super User

Re: Execute a mesure based on a condition

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

AndresSalomon Regular Visitor
Regular Visitor

Re: Execute a mesure based on a condition

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.

PaulDBrown Senior Member
Senior Member

Re: Execute a mesure based on a condition

@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!

 

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 400 members 3,372 guests
Please welcome our newest community members: