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
Shelley
Continued Contributor
Continued Contributor

How to Calculate Actual Sales to Plan, with Time Based on Slicer Selection

Hi All, I'm probably missing something in my thinking, but I never had such issues with time based calculations in QlikView. I want to be able to compare Sales to Plan, based on slicer selections. If no slicers are selected, I want it to be YTD Sales vs YTD Plan (this can be cumbersome because we usually have full year plan and partial year actuals). If slicers are selected, like last year, I want all the results to automatically shift backwards, comparing last year's actuals with the plan. To make matters worse, we are on a 9/30 fiscal year end. Is anyone aware of a blog or post that addresses how to do this or can you provide some input here?

 

I Have a YTD Flag = 1 in the Visual Level Filters Well that makes this look only at the months that fall into the YTD bucket (we look at monthly buckets).

 

So I can calculate Current FY Orders compared to plan, using the YTD flag above and this formula. It works with month and quarter slicers. It doesn't work if I select the prior fiscal year in the slicer.

Current FY Order $ to Plan (%) =
CALCULATE((SUM(OrdersView[OrderValue_Converted]) - SUM('Goals'[OrdersRepair])) / SUM('Goals'[OrdersRepair]),
FILTER('RA_Daily_Calendar','RA_Daily_Calendar'[Flag: Current Fiscal Year] = 1))

 

My really smart teammates hardcode the fiscal years. So, I made an improvement with my approach, but it's still not where I want it to be.  What is the remedy for successful results, using the time/year slicer selection? Thanks!

 

5 REPLIES 5
Greg_Deckler
Super User
Super User

Sample data would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

That being said, you might find my Time Intelligence The Hard Way quick measure useful. See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


@ 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...
Shelley
Continued Contributor
Continued Contributor

@Greg_Deckler THANK YOU!

 

I don't know how this works with non-contiguous months, but it does seem to work even if I select random months out of order. I have to calculate orders with different characteristics. So, I used your YTD measure and then created another measure using filters to get what I need. I don't know if this will bog down Power BI or not, but it does appear to calculate properly even when I try crazy combinations in the time slicers.

 

First, I have:

Total Order Value Converted = SUM('OrdersView'[OrderValue_Converted])

 

TITHW_TotalYTDHW =

VAR __MaxYear = MAX('RA_Daily_Calendar'[Fiscal_Year])

VAR __MaxMonth = MAX('RA_Daily_Calendar'[Fiscal_Month])

VAR __TmpTable = CALCULATETABLE('RA_Daily_Calendar',ALL('RA_Daily_Calendar'[Fiscal_Year]),All('RA_Daily_Calendar'[Fiscal_Month]))

RETURN SUMX(FILTER(__TmpTable,[Fiscal_Year]=__MaxYear && [Fiscal_Month] <= __MaxMonth),

    'OrdersView'[Total Order Value Converted]) 

 

TITHW_TotalYTDHW_TC=
CALCULATE([TITHW_TotalYTDHW],

    FILTER('OrdersView', 'OrdersView'[SBU] = "RSS"),

    FILTER('OrdersView', 'OrdersView'[ProfitCenterGroup] = "ES" || 'OrdersView'[ProfitCenterGroup] = "TC"),

    FILTER('QBContract', 'QBContract'[Techconnect Type] = "Single Site"))

 

Even tried the Quarter slicer and it still seems to be working as I'd expect. Wow. You're brilliant. I've been searching for a solution like this for over a year. Thank you!!!!!!!!!!!!!!!!!!!! 

 

Now hopefully, I can figure out how to use this with the crazy product filters and successfully compare to plan and last year in all the contortions I need.

Cool, glad you find it useful! It's definitely my go to when doing time intelligence calculations!


@ 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...
Shelley
Continued Contributor
Continued Contributor

@Greg_Deckler

It is great; however, I found it doesn't work quite right when I have partial year actuals and full year plan loaded to the tables, in calculating YTD plan. So, I modified this a bit to look at maximum actual data month (our data is refreshed monthly) in calculating the Plan. It seems to work. I wonder if it will be reliable? Or is this how I should be doing it in the first place, perhaps?

 

Total AOP Orders YTD $ =
VAR __MaxYear = MAX('RA_Daily_Calendar'[Fiscal_Year])
//VAR __MaxMonth = MAX('RA_Daily_Calendar'[Fiscal_Month]) USE MAX ACTUAL Data Fiscal Month
VAR __MaxMonth = (LOOKUPVALUE('RA_Daily_Calendar'[Fiscal_Month], 'RA_Daily_Calendar'[Date], MAX('OrdersView'[Line_Creation_Date])))
VAR __TmpTable = CALCULATETABLE('RA_Daily_Calendar',ALL('RA_Daily_Calendar'[Fiscal_Year]),All('RA_Daily_Calendar'[Fiscal_Month]))
RETURN SUMX(FILTER(__TmpTable,[Fiscal_Year]=__MaxYear && [Fiscal_Month] <= __MaxMonth),
'Goals'[AOP Orders])

Shelley
Continued Contributor
Continued Contributor

@Greg_Deckler Thanks for your suggestions.

 

I wish there was an easier way for me to share sample data, but I haven't discovered it, especially when there are many tables. I do have a full calendar table that contains some "Flag" Columns for those dates that should fall into a YTD bucket and those that should fall into a current fiscal year bucket (the flag is 1 if true, 0 if false). I am linking fact tables thru a "Link" table by a common, unique field. For this example, we'll pretend the orders and plan are in the same table.

 

 Actual OrdersPlan
1/1/201810090
2/1/2018150130
1/1/2017125125
2/1/20179590
1/1/2016200180
2/1/2016250225

 

Fiscal Year Time Slicer 2016 / 2017 / 2018

 

When nothing is selected, I want to use 2018 or the maximum year for which there is data to be used by default. So, if no fiscal year is selected the plan to actual performance should show 2018 ((100+150) - (90+130))/(90+130) = 13.6%

 

If I now click on 2017 in the slicer, I want it to automatically run backwards and show 2017  ((125+95) - (125+90))/(125+90) = 2.3%

 

I've since tried the SELECTEDVALUE function, like so. 

Selected FY = SELECTEDVALUE('RA_Daily_Calendar'[Fiscal_Year], [Current Fiscal Year])

 

The default selection for the SELECTEDVALUE function works when I watch the measure on it's own and click the slicer. No clicks and it defaults to 2018. When I click on 2018, it still shows 2018 and when I click on 2017, it shows 2017. HOWEVER, when I add this measure into the following measure, it seems like it's not working.

 

Trying to calculate orders as a first step.

Selected FY Product Order $ =
CALCULATE(
SUM(OrdersView[OrderValue_Converted]),
FILTER('RA_Daily_Calendar', 'RA_Daily_Calendar'[Fiscal_Year] = [Selected FY]),
FILTER('OrdersView', 'OrdersView'[SBU] = "RSS"),
FILTER('OrdersView', 'OrdersView'[ProfitCenterGroup] = "ES" || 'OrdersView'[ProfitCenterGroup] = "TC"),
FILTER('QBContract', 'QBContract'[Techconnect Type] = "Single Site")

)

 

 

When no fiscal years are selected, it adds them all together. When I click on 2017, then it works properly, and the same with clicking on 2018.

 

Am I missing something in how the expression is evaluated? Or do I need another command in here somewhere?

 

Thanks.

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.