cancel
Showing results for
Did you mean:
Highlighted
Frequent 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

Accepted Solutions
New Contributor

## Re: DAX TOTALYTD on two different cadandars

 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
Senior Member

## Re: DAX TOTALYTD on two different cadandars

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
Member

## Re: DAX TOTALYTD on two different cadandars

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

New Contributor

## Re: DAX TOTALYTD on two different cadandars

 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.

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 134 members 1,711 guests
Recent signins: