cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ross73312 Super Contributor
Super Contributor

Re: Variances between columns

For trouble shooting purposes, create a new measure with the same code that instead of having "LastForecast - FirstForecast" change it to "LastForecast".  Put that measure into your visuals and see what numbers come up.  Now measure that part to "FirstForecast", also do a similar check with 'StartDate' and 'EndDate'

 

Are you getting some numbers or are you getting 0?  Are the Start and End Dates coming up with the correct values?

 

If 0:

Check the values in column 'SubmissionType', are the values exactly "Rolling Forecast", including that capitalization and no extra spaces?

 

 


   

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

Proud to be a Datanaut!


   


jbartlett Member
Member

Re: Variances between columns

Yep was already testing in that way

 

both FirstForecast and LastForecast are producing the same numbers (hence the zeros).

 

Here is a copy of the visualisation with the return just being the LastForecast. Note in the submission filter in this example i have selected July 2017 and November 2017

PBI Screenshot.PNG

jbartlett Member
Member

Re: Variances between columns

Sorry yhat last visual was the zeros I was referring to... Here is with just returning LastForecast

PBI Screenshot.PNG

Ross73312 Super Contributor
Super Contributor

Re: Variances between columns

Ok so that tells me that for each ForecastMonth, the First and Last Date are coming back with the same date.  On the brightside, that suggests that the formula overall is working. Its now a matter of understanding why, on a row by row context, we are only seeing a single Submission Month date.


   

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

Proud to be a Datanaut!


   


jbartlett Member
Member

Re: Variances between columns

Think i got it working... just re typed the LASTDATE function and it worked. Must of been a typo somewhere

jbartlett Member
Member

Re: Variances between columns

Can I ask another related question. I now have the measures working but due to the set up the variances measure I am having to put these measures on a separate visual to the forecasts (see screenshot)

 

You can see I have a matrix displaying my July and October forecast submissions and I have a second visual doing the variance between the 2 forecasts (per per solution given for revenue I just replicated for EBIT and Margin %). This vall works fine but ideally I would be better on one visual. Can you think of a possible way?

PBI Screenshot.PNG

Ross73312 Super Contributor
Super Contributor

Re: Variances between columns

I feel like you can by using ALLSELECTED and potentially using another variable to remember the forecast month row context which i'm expecting will be lost when ALLSELECTED is used.

 

To make use of ALLSELECTED, it would look something like:

LastForecast = CALCULATE(
	sum('DPLImportPBI'[Forecast Revenue]),
	ALLSELECTED('DPLImportPBI'),
	'DPLImportPBI'[SubmissionType] = "Rolling Forecast",
	'DPLImportPBI'[Submission Month] = EndDate
) 

I'm expecting if you use the above as is, your forecast month row context could be lost. If thats the case, just add a new variable in your formula with LASTDATE and that column, then put it into the calculate formula as another filter condition along with the 3 in the above.


   

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

Proud to be a Datanaut!


   


jbartlett Member
Member

Re: Variances between columns

Sorry this doesn't achieve what I'm looking for and maybe I didn't explain correctly.

 

For started the ALLSELECTED option pulls in all revenuve for all forecasts and I definitley do not want that.

 

The main issue I have it that the matrix visual has a 2 tiered colum heading. It has July 2017 and November 2017 (becasue these are the 2 submission months I have chosen in my slicer. Below these are the column headers for my measues (RF Revenue, RF EBIT & RF Margin).

 

When I add the varaince measures it tries to give a variance for the July forecast and also a varaince for the November forecast. I do not want this... I want one varaince comparing the 2 forecast. This works perfectly ok as a seperate visual but not if I try and add it to the existing visual

Ross73312 Super Contributor
Super Contributor

Re: Variances between columns

ALLSELECTED is likely the avenue you'll need to persist with, but it won't be enough on its own. As you have correctly pointed out, it will strip extra context away.  You can first record this context into additional variables and add back into your calculate statement.  It will just simply take some trial and error for you to get right.

 

Really comes down to the choice of whether the 2 visuals are going to be enough or whether you wish to spend the time and effort in adding additional complexity in order to squeeze it into the single visual.  Its a bit hard to solve from afar so its one of those problems you will need to pick up and run with.

 


   

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

Proud to be a Datanaut!


   


Helpful resources

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.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 26 members 945 guests
Please welcome our newest community members: