cancel
Showing results for
Did you mean:
Regular Visitor

DAX TOTALYTD on two different cadandars

I am looking to create a YTD total, however, the year end date needs to change depending on the value in another column.

In other words, the fiscal year for group 1 would be from 11-1 (Nov-1) to 10-31 (Oct-31), while the fiscal year for group 2 would be from 7-1 (Jul-1) to 6-30 (Jul-30). What I need to do is when calculating the fiscal year, I need the calculation for that year to be different depending on what group the line item is in. So 2015 for group 1 would be 2014-11-01 to 2015-10-31 while 2015 for group 2 would be 2014-07-01 to 2014-06-30.

Please see an example table here (Please note that I do have a date table related to this one in order to create date functions):

```             **Table 1**
-------------------------
Group |    Date    | Qty
1   | 2014-10-01 |  1
1   | 2014-11-01 |  1
1   | 2015-01-01 |  2
1   | 2015-05-01 |  1
1   | 2015-10-31 |  2
1   | 2015-11-01 |  1
2   | 2014-06-01 |  1
2   | 2014-07-01 |  1
2   | 2014-12-25 |  2
2   | 2015-01-01 |  1
2   | 2015-06-30 |  2
2   | 2015-07-01 |  1```

With this information in mind, I need to create a YTDTOTAL function that will dynamically change the ,<year_end_date> parameter depending on what group the line item is in. I thought of using an if statement, but realized that it wouldn't work on a measure. Something like this:

`Total \$ Sold YTD = TOTALYTD([TOTAL \$ Sold],directSQLDateTable[date],ALL(directSQLDateTable[date]), IIF([GROUP = "A","10/31","6/30"))`

In the end, I would like to create an output similar to this (The "Group A YTD" and "Group B YTD" columns really are not needed, just wanted to add to demonstrate my example):

```    Year-Month | Total_Qty | Group A YTD | Group B YTD
--------------------------------------------------
2014-07  |    1      |      0      |      1
2014-08  |    1      |      0      |      1
2014-09  |    1      |      0      |      1
2014-10  |    2      |      1      |      1
2014-11  |    2      |      1      |      1
2014-12  |    4      |      1      |      3
2015-01  |    7      |      3      |      4
2015-02  |    7      |      3      |      4
2015-03  |    7      |      3      |      4
2015-04  |    7      |      3      |      4
2015-05  |    8      |      4      |      4
2015-06  |    10     |      4      |      6
2015-07  |    5      |      4      |      1
2015-08  |    5      |      4      |      1
2015-09  |    5      |      4      |      1
2015-10  |    7      |      6      |      1
2015-11  |    2      |      1      |      1
2015-12  |    2      |      1      |      1```

Please let me know if you have any questions. My apologies ahead of time if I didn't do that great of job explaining this or if I have left out a piece of info.

Thanks for any advice/help in advance! You guys on here are the best!

1 ACCEPTED SOLUTION
Resident Rockstar

 TOTALYTD() includes everything you need for this.```TotalQty:= SUM(Table1[Qty]) QtyYTDGroup1:= TOTALYTD( [TotalQty] ,DimDate[Date] ,Table1[Group] = 1 ,"10/31" ) QtyYTDGroup2:= TOTALYTD( [TotalQty] ,DimDate[Date] ,Table1[Group] = 2 ,"6/30" ) TotalQtyYTD:= [QtyYTDGroup1] + [QtyYTDGroup2]```I can provide a detailed explanation if you want, but I think the function definition pretty much covers it.
3 REPLIES 3
Resident Rockstar

 TOTALYTD() includes everything you need for this.```TotalQty:= SUM(Table1[Qty]) QtyYTDGroup1:= TOTALYTD( [TotalQty] ,DimDate[Date] ,Table1[Group] = 1 ,"10/31" ) QtyYTDGroup2:= TOTALYTD( [TotalQty] ,DimDate[Date] ,Table1[Group] = 2 ,"6/30" ) TotalQtyYTD:= [QtyYTDGroup1] + [QtyYTDGroup2]```I can provide a detailed explanation if you want, but I think the function definition pretty much covers it.
Memorable Member

You can try switcing calculations on a measure with SWITCH or IF but is more advanced. In my opinion you can create one YTD for each group and them sum up them with a third measure.

like:

```Group 1 YTD =
CALCULATE (
TOTALYTD (
[TOTAL \$ Sold];
directSQLDateTable[date];
ALL ( directSQLDateTable[date] );
"6-30"
);
Table1[Group] = 1
)

Group 2 YTD =
CALCULATE (
TOTALYTD (
[TOTAL \$ Sold];
directSQLDateTable[date];
ALL ( directSQLDateTable[date] );
"10-31"
);
Table1[Group] = 2
)

Total YTD = [Group 1 YTD] + Group 2 YTD]```

Konstantinos Ioannou

I agree with @konstantinos.  I would use different measures.

Announcements

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

Power BI March 2023 Update

Find out more about the March 2023 update.

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors