DAX TOTALYTD on two different cadandars

[nhanser]

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!

[greggyb · Accepted solution]

Please see the answer I provided in your Stackoverflow post:

0 down vote

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.

[konstantinos]

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]

[ALeef]

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