cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nhanser Frequent Visitor
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
greggyb New Contributor
New Contributor

Re: DAX TOTALYTD on two different cadandars

Please see the answer I provided in your Stackoverflow post:

 

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

Re: DAX TOTALYTD on two different cadandars

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

greggyb New Contributor
New Contributor

Re: DAX TOTALYTD on two different cadandars

Please see the answer I provided in your Stackoverflow post:

 

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.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 134 members 1,711 guests
Please welcome our newest community members: