Problem in calculating Year Totals

DAX DAX Calculations
1

Hi, I am struggling to calculate year totals for comparison in a table against monthly totals for that year, say to see what contribution each month made to the full year total (eg 2020). When I use the Filter function with Calculate [Sales] or Calculate [All Sales] I end up with the Monthly figure or the Grand Total for all sales not the annual figure.

The problem is clear in the attached file.

PracticeModel.pbix|attachment](upload://qQPlwuawxVdxfaZTwey3Q7ologU.pbix) (405.3 KB)

Thank You

2

@MIchaelGordon,

No file attached?

Here is what a typical YTD measure looks like:

Sales YTD =
CALCULATE ( [Sales], DATESYTD ( 'Date'[Date] ) )

Thanks
Jarrett

3

Sorry, should now be attached

PracticeModel.pbix (405.3 KB)

Thanks MG

4

@MIchaelGordon,

Here is revised version with Sales & Sales YTD working correctly. Let me know if you need anything else.

image

Thanks
Jarrett

PracticeModel.pbix (406.3 KB)

5

My apologies for not being as clear as I should. I am not looking for YTD but rather the annual total at each level as shown in the attached picture. Ultimately I can then calculate the share of the total attributable to each month. And then I should be repeat the process for all years over a series.

image

Thanks for the help.

6

@MIchaelGordon,

I’m a little confused now. You want a MTD total for each month, or do you want it to be a running YTD for each Month? All you would need to do is replace the Date column with the Month column in my solution, and it will give you a running YTD total. Let me know if you need something different.

Thanks
Jarrett

7

Hi Jarrett,

I appreciate your help. I have monthly totals. What I am missing is the end of year total. At the end of the day, all I want to do is calculate each month’s share of the total. I might want, for example to compare July’s share of sales over several years

image

I hope this helps. MG

8

Hello @MIchaelGordon,

Thank You for posting your query onto the Forum.

Is this the result that you are looking for? Below is the screenshot provided for the reference.

Problem in Calculating Year Totals
Problem in Calculating Year Totals779×725 164 KB

Here’s the formula that I’ve used to for calculating the All Sales for that particular year -

All Sales = 
VAR SelectedYear = SELECTEDVALUE( Dates[Year] )

RETURN
IF( ISBLANK( [Sales] ) , 
    BLANK() ,
        CALCULATE( [Sales] , 
            FILTER( ALL( Dates ) , 
                Dates[Year] = SelectedYear ) ) )

I’m also attaching the PBIX file for the reference.

Hoping you find this useful and helps you to meet your requirements that you are looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

PracticeModel.pbix (406.4 KB)

9

Harsh

Thank you very much, exactly what I was looking for. I built a matrix over three years and successfully calculated for each month the contribution to the respective year total. Perfect.

I would have thought that this would be a very common task but have not seen any explanation on how to do this anywhere. Thanks again.

MG

10

Hello @MIchaelGordon,

You’re Welcome. :slightly_smiling_face:

I’m glad I was able to help you.

Thanks and Warm Regards,
Harsh

11

Good morning,

Yesterday’s solution worked perfectly with a single variable but when I added a second variable in a matrix. I encountered an unexpected problem. I was expecting that the Year Sales for each country, in each month would show the total of all countries (ie $5,762,134.30) but rather I end up with the annual total for each country. Useful but not what I was looking for. You can see what is happening in the first screenshot.

image
image2534×1548 269 KB

The second problem is the the Contrib/Year is based on the Total Sales (of all Years) for each individual country rather than the total of all sales for that year. seond screenshot.

image
image2544×1528 268 KB

In simple terms, what I am trying to do is set up a table that shows me the monthly contribution to sales for each region for a partiuclar year and then caculate that as a ratio. Australia (Jan)/ All Regions Year total for example.

The file is attached.

SportingEquipment.pbix (1.9 MB)

I hope you forgive newbie who is struggling to understand a process for analysing how a measure is working and how to solve the problem.

Cheers MIchael Gordon

12

Hi @MIchaelGordon,

Check out the resource below
https://forum.enterprisedna.co/t/all-allexcept/4913

I hope this is helpful

1 Like
13

Thank you everbody for your help.

I solved the problem of getting the aggegate sales for the year across all countries by wrapping the Year Sales measure (which aggregates the total sales for each year for each country) into a another measure.

Year Sales 2 = CALCULATE([Year Sales], ALL(Territories[Country]))

But this seems messy and I would have thought dooable in a single measure.

In the attached image, I am trying to get the Year Sales 2 outcome (total sales for all countries for the year) with the Years Sales 3 measure but without the intermediate Years Sales measure.

image

I have tried to build a measure but I don’t understand how to add two variables into the filter function.

image

SportingEquipment.pbix (1.9 MB)

Thanks again

MIchael Gordon

14

Hi @MIchaelGordon,

There are many ways to calculate this but I find using an iterator and specifying the tables helps me better understand what is going on. Then you’ll get something like this:

Sales by Year and All Territories = 
CALCULATE(
    SUMX(
        Sales,
        Sales[SalesAmount]
    ),
    FILTER(
        ALL( 'Calendar' ),
        'Calendar'[Year] IN VALUES( 'Calendar'[Year] )
    ),
    ALL( Territories )
)

and

Sales by Year and Country = 
CALCULATE(
    SUMX(
        Sales,
        Sales[SalesAmount]
    ),
    FILTER(
        ALL( 'Calendar' ),
        'Calendar'[Year] IN VALUES( 'Calendar'[Year] )
    ),
    FILTER(
        ALL( Territories ),
        Territories[Country] IN VALUES( Territories[Country] )
    )
)

I hope this is helpful.