cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Highlighted
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.

View solution in original post

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.

Highlighted
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.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 98 members 1,392 guests
Please welcome our newest community members: