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

Total when selecting multiple months and month used in calculation

Hi all

 

I have a fairly straight forward model with three fact tables and few dimensions. One of the dimension is a version table that contains if the row is an actual or a forecast version. Those two tables are related on an id. I´m trying to create a measure that has the following conditions:

If the chosen month is less than last forecast month (5 at the moment) then use actuals otherwise use the current forecast.

I have managed to create the measure that works well unless you choose multiple months and one of the month falls on each side of the condition above. So say you choose JAn, Feb, Mar, Apr and May 2019 the measure should show you actuals for the first four and forecast for the last one. Again my measure works when looking at individual months but the total does not give me the correct results. I know why it isn´s as I´m using MAX(selected month) to see if the selected month should be actual or forecast which works fine when looking at individual months but will give you May when looking at the total and therefore the total is giving me all forecast. 

 

CALCULATE(SUM(ExFactorySales[Net Sales Value EUR]),FILTER('Version','Version'[VersionKey]=IF(MAX('Calendar'[Year Code])<IF(MONTH(TODAY())=1,YEAR(TODAY())-1, YEAR(TODAY())),"ACTUAL",IF(MAX('Calendar'[Month Number])<IF(MONTH(TODAY())>9,10,IF(MONTH(TODAY())<5,1,5)),"ACTUAL","Current Forecast"))))

The above is my measure with the part in red as the problem area. Before anyone asks I also have it with variables as well but just wanted to put it in here as a simple measure. 

 

I tried using SUMX like below to iterate through the months and then sum them but since the Calculate is evaluating the whole thing the MAX is still giving me the same issue

SUMX(
VALUES('Calendar'[Cut -off Month]),
CALCULATE(SUM(ExFactorySales[Net Sales Value EUR]),FILTER('Version','Version'[VersionKey]=IF(MAX('Calendar'[Year Code])<IF(MONTH(TODAY())=1,YEAR(TODAY())-1, YEAR(TODAY())),"ACTUAL",IF(MAX('Calendar'[Month Number])<IF(MONTH(TODAY())>9,10,IF(MONTH(TODAY())<5,1,5)),"ACTUAL","Current Forecast")))))

Can anyone help me here? As a last resort I will prevent them from selecting multiple months but that would not be ideal.

 

I have tried all kinds of aggregations for the month number but none of them works. SelectedValue gives you nothing evaluating the total as there are 5 months in context. 

 

Ásgeir

7 REPLIES 7
Anonymous
Not applicable

Why don't you create a column in your fact table that will hold the right amount for each row? Once you have it, the measure you want will be a simple SUM over the column... No conditional logic required. All you have to do is to create the right logic in your calculated column.

Best
Darek

Hi Darek

 

I did think about that but I have around 20 versions in my real data. Each month might have multiple versions so I cannot add the correct amount for each row and then sum it as the there would be multiplications.

 

Example:

 

Capture.PNG

 

I would get 1350 if I sum the Correct amount column

Anonymous
Not applicable

Well, not really 🙂 However many versions you have, you can always get the right amount by filtering (in the measure) for the right version. You could also have a fact where you have actuals only and a fact where you only store forecasts. That would make it a better model right off the bat. You could also have another fact that would implement the logic you want. All these would be linked to the same dimensions and the measures would calculate the correct figures by whatever logic you want.

Best
Darek

Hi Darek

There are probably around 20 version. 5 new versions created a year. The issue is that not all versions have data for all months. Actuals has only data for past months while the newest forecast version has data for May onwards. So there is no one version to filter on.

I know I might achieve this with a different model but I was hoping to not have to re-model. In the end I might be forced into it though
Anonymous
Not applicable

First of all you have to get the model RIGHT. Without this you'll be suffering big time. Let this sink in: FIRST THE MODEL, THEN THE DAX. And you can take my word for that---I have some heavy and very formal training in building data models (not only in Tabular). Not the other way round.

In Power Query you can do anything you want with the data. So, even though you have data for some months for some versions only, you can "fill in the blanks" and make all the versions have data for all the months. If there's no data, then put in 0 or BLANK. This way you'll standardize your versions (standardization is ALWAYS a good thing). Once you've got this, you can create calculated columns (also in PQ, not in DAX) that will give you the right amount for your calculations. Your DAX will then be as easy as SUM ( T[Column] ) with---maybe---some simple filters.

Always remember: FIRST THE MODEL, THEN DAX and you'll be happy.

Best
Darek

Hi Stachu

 

Here is more context. As I said in my orginal message the calculation is correct on the month level but wrong on the total level. You can see in the image below more clearly what the structure is and what the problem is. You can also download a simplified version of the pbix and and excel file that is it's source from here

 

Capture.PNG

Stachu
Community Champion
Community Champion

I think you should use 'Calendar'[Cut -off Month] as the reference point, instead of TODAY()

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1Column2
A1
B2.5

What are the relationships between 'ExFactorySales', 'Version' and 'Calendar'?

if there is no overlap in your data between "ACTUAL" and "Current Forecast", ,i.e. for each month there is either a actual figure or forecast figure (but never both) then this should work:

Measure =
CALCULATE (
    SUM ( 'ExFactorySales'[Net Sales Value EUR] ),
    'Version'[VersionKey] = "ACTUAL", 
--'Calendar'[Month Number] <= MAX('Calendar'[Cut -off Month]) ) + CALCULATE ( SUM ( 'ExFactorySales'[Net Sales Value EUR] ), 'Version'[VersionKey] = "Current Forecast"
--'Calendar'[Month Number] > MAX('Calendar'[Cut -off Month]) )

if there is overlap, then you can uncomment filter on the Calendar table, but I cannot be sure it works without having more info on your model (but I think you get the general idea)

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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.

Top Solution Authors