locked
How to use RELATED in DAX RRS feed

  • Question

  • Hello PowerPivot and DAX experts,

    I'm sorry that I can't attached images or links since my account still awaits to verify.

    Visualize that I have 3 factTables (Turnover, FIN Trial Balance & Forecast Details) and a 'Calendar DateTable' that I've created

    3 factTables - Turnover, FIN Trial Balance and Forecast Details

    Turnover Fields/Columns - ... ... ... Calendar Date, Fiscal Calendar

    FIN Trial Balance Fields/Columns - ... ... ... Calendar Date, Fiscal Calendar

    Forecast Details Fields/Columns - ... ... ... Date, Fiscal Calendar Date

    Calendar DateTable Fields/Columns - ... ... ... Calendar Date

    'Turnover'[Calendar Date] = 'Calendar DateTable'[Calendar Date]

    'FIN Trial Balance'[Calendar Date] = 'Calendar DateTable'[Calendar Date]

    'Forecast Details'[Date] = 'Calendar DateTable'[Calendar Date]

    My factTables dataset consists of multiple different Fiscal Year End. i.e. Dec Year End, Apr Year End etc.

    Thus, in my 'FIN Trial Balance', I have 2 dates column/field - Calendar Date and Fiscal Calendar (or Fiscal Calendar Date in 'Forecast Details' factTable). The difference between them are, Calendar Date is as the name suggest, reflect the date in our calendar, whereas, Fiscal Calendar is what I need to use in order to determine the MTD and YTD calculation in order to take the Fiscal Year End into consideration.

    For example, an April Year End, starts with Calendar May 2017 and ends in Calendar Apr 2018. Thus Fiscal Calendar would take May 2017 as month 1 of year 2017, Jun 2017 as month 2 of year 2017, ... ... ..., Mar 2018 as month 11 of year 2017 and Apr 2018 as month 12 of year 2017

    I created a calculated field name YTD where the DAX formula is:

    =CALCULATE(

                           [Final Figures],

                           ActualBudget[Actual/Budget] = "Actual",

                           DATESYTD('Calendar DateTable'[Calendar Date])

    )

    The above DAX formula works only if calendar date = fiscal calendar, in a sense it's a Dec Year End.

    My question thus is, how do I create a 'RELATED' so that when user clicks on a particular value that's in the 'Calendar DateTable', it knows to look for the Fiscal Calendar that's in the other factTables.

    Tuesday, November 21, 2017 4:29 PM

Answers

  • My question thus is, how do I create a 'RELATED' so that when user clicks on a particular value that's in the 'Calendar DateTable', it knows to look for the Fiscal Calendar that's in the other factTables.

    With the model you currently have you can't easily do this. The best approach is to create a separate 'fiscal calendar' lookup/dimension table and you would then create a relationship between this table and each of your fact tables. You could then use RELATED() against this new table.



    http://darren.gosbell.com - please mark correct answers

    Wednesday, November 22, 2017 12:39 AM
  • Hi Dluhut,

    Thanks for your question.

    I would suggest you to use below DAX formula:

    FINFiscalYTD =
    CALCULATE (
        SUM ( 'Final'[Final Figures] ),
        ActualBudget[Actual/Budget] = "Actual",
        DATESYTD ( 'Calendar DateTable'[Calendar Date], "04-30" ),
        ALL ( 'Calendar DateTable' )
    )

    For more information, please refer to below articles:
    https://www.powerpivotblog.nl/using-time-intelligent-powerpivot-functions-with-fiscal-year/
    https://msdn.microsoft.com/en-us/library/ee634400.aspx

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, November 22, 2017 6:04 AM
  • Assume if I were to create a separate 'fiscal calendar' table, and link all factTables to it, how do I create a relationship that's from the 'Calendar DateTable' to 'Fiscal Calendar'?

    I can't think why you would want to do this. I was assuming that you had 2 separate columns in your fact tables because they needed to be filtered independently. Maybe you need to post a few rows of example data and some examples of how you want to summarize it as this questions makes me wonder if there is something about your data model that I'm not understanding correctly.

    http://darren.gosbell.com - please mark correct answers

    Wednesday, November 22, 2017 11:22 PM
  • I just missed off the date column as the first parameter

    =CALCULATE([Final Figures],

                         ActualBudget[Actual/Budget] = "Budget",
                         DATESBETWEEN(                     'Calendar DateTable'[Calendar Date],
                         STARTOFYEAR('Calendar DateTable'[Calendar Date], "04-30"),  
                         ENDOFYEAR('Calendar DateTable'[Calendar Date], "04-30")))


    http://darren.gosbell.com - please mark correct answers

    Monday, December 4, 2017 7:13 PM

All replies

  • My question thus is, how do I create a 'RELATED' so that when user clicks on a particular value that's in the 'Calendar DateTable', it knows to look for the Fiscal Calendar that's in the other factTables.

    With the model you currently have you can't easily do this. The best approach is to create a separate 'fiscal calendar' lookup/dimension table and you would then create a relationship between this table and each of your fact tables. You could then use RELATED() against this new table.



    http://darren.gosbell.com - please mark correct answers

    Wednesday, November 22, 2017 12:39 AM
  • Hi Dluhut,

    Thanks for your question.

    I would suggest you to use below DAX formula:

    FINFiscalYTD =
    CALCULATE (
        SUM ( 'Final'[Final Figures] ),
        ActualBudget[Actual/Budget] = "Actual",
        DATESYTD ( 'Calendar DateTable'[Calendar Date], "04-30" ),
        ALL ( 'Calendar DateTable' )
    )

    For more information, please refer to below articles:
    https://www.powerpivotblog.nl/using-time-intelligent-powerpivot-functions-with-fiscal-year/
    https://msdn.microsoft.com/en-us/library/ee634400.aspx

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, November 22, 2017 6:04 AM
  • Hi Willson,

    Thanks for your reply.

    I don't understand part of the DAX formula that you wrote.

    First of all, I don't have a 'Final' Table, thus, how do I incorporate this 'Final'[Final Figures]?

    Secondly, the formula DATESYTD('Calendar DateTable[Calendar Date], "04-30"), the latter part "04-30", what does it do actually? Does it calculate from Jan 1, 20xx to Apr 30, 20xx only?

    Wednesday, November 22, 2017 2:33 PM
  • Assume if I were to create a separate 'fiscal calendar' table, and link all factTables to it, how do I create a relationship that's from the 'Calendar DateTable' to 'Fiscal Calendar'?

    Would my current YTD formula still works knowing that the data is 'Dec', 'Apr' or 'Aug' Year End etc?

    Wednesday, November 22, 2017 2:38 PM
  • Assume if I were to create a separate 'fiscal calendar' table, and link all factTables to it, how do I create a relationship that's from the 'Calendar DateTable' to 'Fiscal Calendar'?

    I can't think why you would want to do this. I was assuming that you had 2 separate columns in your fact tables because they needed to be filtered independently. Maybe you need to post a few rows of example data and some examples of how you want to summarize it as this questions makes me wonder if there is something about your data model that I'm not understanding correctly.

    http://darren.gosbell.com - please mark correct answers

    Wednesday, November 22, 2017 11:22 PM
  • Hi Willson,

    Thanks for your reply.

    I don't understand part of the DAX formula that you wrote.

    First of all, I don't have a 'Final' Table, thus, how do I incorporate this 'Final'[Final Figures]?

    Secondly, the formula DATESYTD('Calendar DateTable[Calendar Date], "04-30"), the latter part "04-30", what does it do actually? Does it calculate from Jan 1, 20xx to Apr 30, 20xx only?

    You can just replce 'Final'[Final Figures] with the related table name and column that you want to sum for.

    >>>Secondly, the formula DATESYTD('Calendar DateTable[Calendar Date], "04-30"), the latter part "04-30", what does it do actually? Does it calculate from Jan 1, 20xx to Apr 30, 20xx only?

    DATESYTD(<dates> [,<year_end_date>])     see syntax for DATESYTD, "04-30" is the year_end_date.

    Please do check below URL for more detailed information:

    https://msdn.microsoft.com/en-us/library/ee634221.aspx
    https://www.powerpivotblog.nl/using-time-intelligent-powerpivot-functions-with-fiscal-year/


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, November 23, 2017 6:44 AM
  • I was assuming that you had 2 separate columns in your fact tables because they needed to be filtered independently.

    http://darren.gosbell.com - please mark correct answers

    Hi Darren,

    No you're right. In my factTables, I do have 2 dates column, 1 represent the 'calendar', and the other represent the 'fiscal'.

    Like you've said, I've created another calendar table 'Fiscal Calendar DateTable' and create a relationship from each factTable of [Fiscal Calendar] to the newly created 'Fiscal Calendar DateTable'.

    But, even after I've created it, my MTD and/or YTD formula still isn't pulling the correct result, because the value is still looking at the 'Calendar DateTable'.

    I tried to have pictures attached, but due to my account not verified, I'm unable to.

    However, I hope below, a simple relationships table could help out in visualizing how my tables are related.

    Active           Table                                              Related Lookup Table

    Yes               Turnover[Calendar Date]                  Calendar DateTable[Calendar Date]                  

    Yes               Turnover[Fiscal Calendar]                 Fiscal Calendar DateTable[Date]

    Yes               FIN Trial Balance[Calendar Date]       Calendar DateTable[Calendar Date]                  

    Yes               FIN Trial Balance[Fiscal Calendar]      Fiscal Calendar DateTable[Date]

    Yes               Forecast[Date]                                 Calendar DateTable[Calendar Date]                  

    Yes               Forecast[Fiscal Calendar Date]          Fiscal Calendar DateTable[Date]

    Thursday, November 23, 2017 12:58 PM
  • But, even after I've created it, my MTD and/or YTD formula still isn't pulling the correct result, because the value is still looking at the 'Calendar DateTable'.

    I think I understand your model layout, but I don't know what you mean by "isn't pulling the correct result". I don't know what result you are seeing and what result you are expecting. Is it possible to type out 5-10 rows of example data, then show what result you are seeing and what result you are expecting based on that sample data?

    http://darren.gosbell.com - please mark correct answers

    Friday, November 24, 2017 12:56 AM
  • Great that you have understanding of my model.

    What I'm trying to accomplish here is to create a Profit & Loss statement (PnL) via PowerPivot.

    Below is a simple layout of the PnL

                                                                MTD Actual   MTD Budget   YTD Actual   YTD Budget   Forecast   Budget

    Turnover (from factTable Turnover)                x                   x                x                  x                x             x

    +/- Revenues (+/- denotes expand/collapse)

          Gross Rent                                             x                   x                 x                 x                x              x

          Other Income                                         x                   x                 x                 x                x              x

    __________________________________________________________________________________________

    Total Revenues                                             x                   x                 x                 x                 x              x

    __________________________________________________________________________________________

    +/- Expenses

          Advertising                                             x                   x                 x                 x                x              x

          Utilities                                                  x                    x                 x                 x                x             x

    __________________________________________________________________________________________

    Total Expenses                                             x                   x                 x                 x                 x              x

    __________________________________________________________________________________________

    Operating Income                                         x                   x                 x                  x                x               x

    Those 'x' are values that are build via measures piece by piece and not a 1 mega formula. In my 1st thread, where I had "=CALCULATE([Final Figures], ... ", that [Final Figures] are the same as 'x'

    The headers, MTD Actual, MTD Budget, YTD Actual, YTD Budget and Budget are from factTable FIN Trial Balance. As for Forecast, it's derived from the SUM of factTable FIN Trial Balance[Amount] + factTable Forecast Details[Amount]

    As I have stated in my 1st thread, My factTables data consist of multiple different Fiscal Year End. i.e. Dec Year End, Apr Year End etc.

    Taking a sample below, I have a data that has 2 FiscalYearEnd - Dec and Apr. The 'CalendarDate' as the name suggest is a date based on calendar . 'FiscalCalendarDate' on the other hand reflects the specific period based on the CompanyCode's Fiscal Year End, which is what I would like to achieve in calculating the MTD, YTD etc.

    FiscalYearEnd (Dec) Example:

    Assume that I have a slicer where a user can select the month (Jan to Dec), and these months are derived from CalendarDate. User selects the month of Oct. Given the table below, my MTD (Oct) = $26+$27 = $53, my YTD (Oct) = $10+$11+...+$26+$27 = $333

    This is a very straight forward calculation, as my 'CalendarDate' and 'FiscalCalendarDate' are the same where, CalendarDate(Oct) = FiscalCalendarDate(Oct)

    FiscalYearEnd (Apr) Example:

    With the same selection, where user selects for the month of Oct FOR APR Fiscal Year End, The DESIRED result for MTD (Oct) = $265, YTD (Oct) = $185 + $195 + $205 + $225 + $245 + $265 = $1,320.

    This is where I am facing my difficulties. Due to many measures to come up with the [Final Figures], I am getting the wrong result for YTD, The reason being, I am unable to 'RELATE' that the 1st period of the year is CalendarDate(May) not CalendarDate(Jan), as CalendarDate(Jan) is actually FiscalCalendarDate(Sep 2016).

    CompanyCode   FiscalYearEnd   CalendarDate   FiscalCalendarDate   Amount

    1000                      Dec             Jan 1, 2017         Jan 1, 2017           $10

    1000                      Dec             Jan 1, 2017         Jan 1, 2017           $11

    1000                      Dec             Feb 1, 2017         Feb 1, 2017           $12

    1000                      Dec             Feb 1, 2017         Feb 1, 2017           $13

    1000                      Dec             Mar 1, 2017         Mar 1, 2017           $14

    1000                      Dec             Mar 1, 2017         Mar 1, 2017           $15

    1000                      Dec             Apr 1, 2017         Apr 1, 2017           $16

    1000                      Dec             May 1, 2017        May 1, 2017           $17

    1000                      Dec             Jun 1, 2017         Jun 1, 2017           $18

    1000                      Dec             Jun 1, 2017         Jun 1, 2017           $19

    1000                      Dec             Jul 1, 2017          Jul 1, 2017           $20

    1000                      Dec             Jul 1, 2017          Jul 1, 2017           $21

    1000                      Dec             Aug 1, 2017         Aug 1, 2017           $22

    1000                      Dec             Aug 1, 2017         Aug 1, 2017           $23

    1000                      Dec             Sep 1, 2017         Sep 1, 2017           $24

    1000                      Dec             Sep 1, 2017         Sep 1, 2017           $25

    1000                      Dec             Oct 1, 2017         Oct 1, 2017           $26

    1000                      Dec             Oct 1, 2017         Oct 1, 2017           $27

    1000                      Dec             Nov 1, 2017         Nov 1, 2017           $28

    1000                      Dec             Nov 1, 2017         Nov 1, 2017           $29

    1000                      Dec             Dec 1, 2017         Dec 1, 2017           $30

    1000                      Dec             Dec 1, 2017         Dec 1, 2017           $31

    2000                      Apr             Jan 1, 2017         Sep 1, 2016           $105

    2000                      Apr             Feb 1, 2017         Oct 1, 2016           $125

    2000                      Apr             Mar 1, 2017         Nov 1, 2016           $145

    2000                      Apr             Apr 1, 2017         Dec 1, 2016           $165

    2000                      Apr             May 1, 2017         Jan 1, 2017           $185

    2000                      Apr            Jun 1, 2017         Feb 1, 2017           $195

    2000                      Apr             Jul 1, 2017          Mar 1, 2017           $205

    2000                      Apr             Aug 1, 2017         Apr 1, 2017           $225

    2000                      Apr             Sep 1, 2017         May 1, 2017           $245

    2000                      Apr            Oct 1, 2017         Jun 1, 2017           $265

    2000                      Apr             Nov 1, 2017         Jul 1, 2017           $285

    2000                      Apr            Dec 1, 2017         Aug 1, 2017           $305

    I hope the simple layout of my data and 2 examples are sufficient enough to make you have a better understanding of my model, as well as what I am trying to accomplish.

    Friday, November 24, 2017 3:17 PM
  • So the table of data at the end is helpful as I did not realize that FiscalCalendarDate was an actual date, I assumed that it was probably held the content that is actually in the FiscalYearEnd column. But the critical piece of missing information is that we still don't know how you want to aggregate the data across the two companies.

    I can think of at least 2 possible scenarios

    Scenario 1 - you would to align both companies on the same calendar so that when you compare both of them at a given month the YTD figures are comparable (so if you run a report for March they are both 3 months into the year). To do this you would simply either change the relationship for your date table to point at the FiscalCalendarDate column or create an inactive relationship to FiscalCalendarDate.

    CALCULATE(    
        CALCULATE(
             [Final Figures],
             ActualBudget[Actual/Budget] = "Actual",
             DATESYTD('Calendar DateTable'[Calendar Date])
        ),
        USERELATIONSHIP( DateTable[Calendar Date], <table with the data>[FiscalCalendarDate] )
    )

    The other possible scenario is that you want to report everything off the calendar date. This probably makes sense at an individual company level, but is confusing at the grandtotal level as the YTD amounts "reset" at different times. You could do that with a calculation like the following (note that the year is ignored in the DATEYTD function) :

    YTD :=
    VAR ytddec =
        DATESYTD ( 'Calendar'[Date], "2017-12-31" )
    VAR ytdapr =
        DATESYTD ( 'Calendar'[Date], "2017-04-30" )
    RETURN
        SUMX (
            VALUES ( GL[FiscalYearEnd] ),
            IF (
                GL[FiscalYearEnd] = "Apr",
                CALCULATE ( SUM ( GL[Amount] ), ytdapr ),
                CALCULATE ( SUM ( GL[Amount] ), ytddec )
            )
        )

    This is where I am facing my difficulties. Due to many measures to come up with the [Final Figures], I am getting the wrong result for YTD,

    Or the other option is that you might have issues in one of your other measures that is causing problems.


    http://darren.gosbell.com - please mark correct answers

    Sunday, November 26, 2017 10:37 PM
  • But the critical piece of missing information is that we still don't know how you want to aggregate the data across the two companies.

    Scenario 1 - you would to align both companies on the same calendar so that when you compare both of them at a given month the YTD figures are comparable (so if you run a report for March they are both 3 months into the year). To do this you would simply either change the relationship for your date table to point at the FiscalCalendarDate column or create an inactive relationship to FiscalCalendarDate.

    CALCULATE(    
        CALCULATE(
             [Final Figures],
             ActualBudget[Actual/Budget] = "Actual",
             DATESYTD('Calendar DateTable'[Calendar Date])
        ),
        USERELATIONSHIP( DateTable[Calendar Date], <table with the data>[FiscalCalendarDate] )
    )

    You are right Darren. What I'd like is that, when a user select a particular month from the slicer (and this slicer is derived from the Calendar DateTable, the [Final Figures] MTD, YTD calculation will know dynamically how many months it need to calculate based on the Fiscal Year End (Dec, Apr, Jun, Aug etc). And like the example scenario, if it's Apr Year End, the MTD and YTD needs to reflect the correct 'month period' from the Fiscal Calendar DateTable. Thus, if the user select Oct Calendar Date, in an Apr Year End, it's actually the 6th period (or Jun 2017), and the YTD needs to calculate from May to Current Month (Oct) of the user slicer.

    My question would be, for the 'inactive' relationship to the FiscalCalendarDate, what 2 table columns am I linking it to?

    The second question would be, in your DAX formula, where you had <table with the data>[FiscalCalendarDate], since all of my factTables have FiscalCalendarDate column, does it matter which factTable I choose? The reason why I ask this is that, for 'Forecast' Measures, this measure takes from the factTable FIN Trial Balance  and  factTableForecast.

    • Edited by dluhut Monday, November 27, 2017 1:55 AM
    Monday, November 27, 2017 1:47 AM
  • My question would be, for the 'inactive' relationship to the FiscalCalendarDate, what 2 table columns am I linking it to?

    The second question would be, in your DAX formula, where you had <table with the data>[FiscalCalendarDate], since all of my factTables have FiscalCalendarDate column, does it matter which factTable I choose? The reason why I ask this is that, for 'Forecast' Measures, this measure takes from the factTable FIN Trial Balance  and  factTableForecast.


    So if you use the inactive relationship design you need to specify the 2 columns involved in the inactive relationship, which would be the date column in your date dimension table and the FiscalCalendarDate column in your fact table. If you have multiple fact tables you would need to add multiple calls to the USERELATIONSHIP() function inside the outer CALCULATE call (one for each fact table).

    eg.

    CALCULATE(    
        CALCULATE(
             [Final Figures],
             ActualBudget[Actual/Budget] = "Actual",
             DATESYTD('Calendar DateTable'[Calendar Date])
        ),
        USERELATIONSHIP( DateTable[Calendar Date], 'factTable FIN Trial Balance'[FiscalCalendarDate] ),
        USERELATIONSHIP( DateTable[Calendar Date], 'factTableForecast'[FiscalCalendarDate] )
    )

    But if all your reporting is based off the fiscal calendar it might be simpler to just change the relationship between your fact tables and your date dimension to use the FiscalCalendarDate column in the fact tables.


    http://darren.gosbell.com - please mark correct answers

    Monday, November 27, 2017 2:43 AM
  • But if all your reporting is based off the fiscal calendar it might be simpler to just change the relationship between your fact tables and your date dimension to use the FiscalCalendarDate column in the fact tables.


    http://darren.gosbell.com - please mark correct answers

    Yes indeed!!! My reporting is based off the fiscal calendar.

    Which originally, I have all of my factTables[FiscalCalendarDate] is related to the Calendar DateTable.

    But because of this, anything that's not Dec Year End, it's spitting out the wrong MTD and YTD figures.

    Taking the Apr Year End as an example, given that all of my factTables[FiscalCalendarDate] relates to Calendar DateTable, when a user clicks on 'Oct' (this slicer is based on Calendar DateTable), my MTD is empty, while YTD is also the wrong value, because, it's looking at the FiscalCalendarDate as Oct, instead of June (6th period on the Apr Year End), and my YTD calculation is calculated from Jan to Oct, when it's supposed to calculate the YTD from the 1st period to the 6th period.

    Monday, November 27, 2017 2:08 PM
  • Taking the Apr Year End as an example, given that all of my factTables[FiscalCalendarDate] relates to Calendar DateTable, when a user clicks on 'Oct' (this slicer is based on Calendar DateTable), my MTD is empty, while YTD is also the wrong value, because, it's looking at the FiscalCalendarDate as Oct, instead of June (6th period on the Apr Year End), and my YTD calculation is calculated from Jan to Oct, when it's supposed to calculate the YTD from the 1st period to the 6th period.

    So the paragraph above seems to me to contradicts your first statement and this is where I think we are getting confused. To my thinking Oct is the 10th month and if you were to align the two financial calendars the 10th month of the Apr year end would be Feb data for the following year - which should be blank as we don't have data for Feb 2018 yet. 

    If when someone filters for Oct 2017 you want to see the month figures for data that occurred during the calendar month of Oct for both companies and the YTD for company 1000 should show the total of 10 months of data and the YTD for Company 2000 should show 6 months of data then we could do that, but just be aware that the YTD grand total of both companies will be meaningless. (you'd just use the SUMX based expression from my previous post). 



    http://darren.gosbell.com - please mark correct answers

    Tuesday, November 28, 2017 11:39 AM
  • If when someone filters for Oct 2017 you want to see the month figures for data that occurred during the calendar month of Oct for both companies and the YTD for company 1000 should show the total of 10 months of data and the YTD for Company 2000 should show 6 months of data then we could do that, but just be aware that the YTD grand total of both companies will be meaningless. (you'd just use the SUMX based expression from my previous post). 



    Yes!!! That's exactly what I'm trying to do.

    That is, when someone filters for Oct 2017, For company 1000 (which is Dec Year End), the MTD would be $53 and the YTD would be $333 (Jan to Oct <--> 10 months of data USING CALENDAR DATE). For company 2000 (which is Apr Year End), the MTD would be $265 and the YTD would be $1,320 (May to Oct <--> 6 months of data USING CALENDAR DATE).

    And YES!!! I do agree with you that the grand total are meaningless, since I won't need them anyway in the Profit and Loss Statement.

    You mentioned that the SUMX formula would work. However, since I'm using PowerPivot (Not Power BI), I don't think I can use the VAR statement.

    Secondly, ASSUME that I can use the SUMX formula that you've given to me, I ASSUME that I need to create the relationship from factTables[Calendar Date] to Calendar DateTable[Calendar Date]  and  factTables[Fiscal Calendar] to Fiscal Calendar Date[Date]


    Tuesday, November 28, 2017 2:10 PM
  • VAR and RETURN work in Excel 2016, but if you are using an earlier version you can also just inline the expressions from those variables.

    eg.

    YTD :=
    SUMX (
    VALUES ( GL[FiscalYearEnd] ),
    IF (
    GL[FiscalYearEnd] = "Apr",
    CALCULATE ( SUM ( GL[Amount] ), DATESYTD ( 'Calendar'[Date], "2017-04-30" ) ),
    CALCULATE ( SUM ( GL[Amount] ), DATESYTD ( 'Calendar'[Date], "2017-12-31" ) )
    )
    )

    And in terms of the relationships I was just relating the date table to the [Calendar Date] column in the fact. In terms of the calculations you've described in this post I can not see a need for a 'Fiscal Calendar Date' table or the [Fiscal Calendar Date] column in the fact tables.


    http://darren.gosbell.com - please mark correct answers

    Tuesday, November 28, 2017 7:38 PM
  • VAR and RETURN work in Excel 2016, but if you are using an earlier version you can also just inline the expressions from those variables.

    eg.

    YTD :=
    SUMX (
    VALUES ( GL[FiscalYearEnd] ),
    IF (
    GL[FiscalYearEnd] = "Apr",
    CALCULATE ( SUM ( GL[Amount] ), DATESYTD ( 'Calendar'[Date], "2017-04-30" ) ),
    CALCULATE ( SUM ( GL[Amount] ), DATESYTD ( 'Calendar'[Date], "2017-12-31" ) )
    )
    )

    And in terms of the relationships I was just relating the date table to the [Calendar Date] column in the fact. In terms of the calculations you've described in this post I can not see a need for a 'Fiscal Calendar Date' table or the [Fiscal Calendar Date] column in the fact tables.


    http://darren.gosbell.com - please mark correct answers

    Hi Darren,

    Modified the formula a little, and the YTD value works PARTIALLY.

    Wondering if you can help me out.

    Notice that, instead of SUM(OneOfTheFactTable[Amount]), I use [Final Figures], which is derived from multiple measures.

    =SUMX(
                   VALUES('FIN Trial Balance'[Year End]),
                   IF('FIN Trial Balance'[Year End] = "Apr",
                        CALCULATE([Final Figures], DATESYTD('Calendar DateTable'[Calendar Date], "04-30"), ActualBudget[Actual/Budget] = "Actual"),
                        CALCULATE([Final Figures], DATESYTD('Calendar DateTable'[Calendar Date], "12-31"), ActualBudget[Actual/Budget] = "Actual")
                   )
    )

    Is it possible to for a formula that uses 'CALCUALTE' first instead of 'SUMX'? My reasoning is that, if I were to use the SUMX first, then my [Final Figures] Measures only works with PART of the 'PnL Layout'.

    Therefore, am thinking if I can have it like (The formula doesn't work), where the logic is, to use calculate first, follow by [Final Figures] MEasure, then the filter part check for the condition of the Year End, and finally uses the DATESYTD

    =CALCULATE([Final Figures],
                         VALUES('FIN Trial Balance'[Year End]),
                         ActualBudget[Actual/Budget] = "Actual",IF('FIN Trial Balance'[Year End] = "Apr",
                         IF(VALUES('FIN Trial Balance'[Year End]) = "Apr",
                             DATESYTD('Calendar DateTable'[Calendar Date], "04-30"),
                             DATESYTD('Calendar DateTable'[Calendar Date], "04-30")
                         )
    )

    • Edited by dluhut Thursday, November 30, 2017 6:40 PM
    Thursday, November 30, 2017 4:02 PM
  • So the main reason I started with a SUMX was to try to get the grand total working by adding the multiple different year ends together. But at the single company level it should be summing a single row which should return the value of [Final Figures] for that row, so I doubt that swapping from SUMX to CALCULATE will fix whatever "not working" means.

    But I don't think your expression will work as-is because at the grand total level there will be multiple values for Year End which will break the IF expression that is trying to test for "Apr" - you'd have to wrap the whole thing in a HASONEVALUE test.

    eg.

    =IF(HASONEVALUE('FIN Trial Balance'[Year End]),
          CALCULATE([Final Figures],
                         ActualBudget[Actual/Budget] = "Actual",IF('FIN Trial Balance'[Year End] = "Apr",
                         IF(VALUES('FIN Trial Balance'[Year End]) = "Apr",
                             DATESYTD('Calendar DateTable'[Calendar Date], "04-30"),
                             DATESYTD('Calendar DateTable'[Calendar Date], "04-30")
                         )
          )
     )


    http://darren.gosbell.com - please mark correct answers

    Thursday, November 30, 2017 11:22 PM
  • =IF(HASONEVALUE('FIN Trial Balance'[Year End]),
          CALCULATE([Final Figures],
                         ActualBudget[Actual/Budget] = "Actual",IF('FIN Trial Balance'[Year End] = "Apr",
                         IF(VALUES('FIN Trial Balance'[Year End]) = "Apr",
                             DATESYTD('Calendar DateTable'[Calendar Date], "04-30"),
                             DATESYTD('Calendar DateTable'[Calendar Date], "04-30")
                         )
          )
     )


    http://darren.gosbell.com - please mark correct answers

    Just to double confirm,

    The formula that you wrote there is ONLY to test if Year End is "Apr", that's why I have 2 DATESYTD that ended with "04-30". In my real data, I have 4 different Year End, 'Dec', 'Apr', 'Jun' and 'Aug'.

    If I want to incorporate those Year Ends, would the formula be like this?

    =IF(HASONEVALUE('FIN Trial Balance'[Year End]),
          CALCULATE([Final Figures],
                         ActualBudget[Actual/Budget] = "Actual",
                         IF('FIN Trial Balance'[Year End] = "Apr",
                         IF(VALUES('FIN Trial Balance'[Year End]) = "Apr",
                             DATESYTD('Calendar DateTable'[Calendar Date], "04-30"),
                             DATESYTD('Calendar DateTable'[Calendar Date], "04-30")),
                         IF('FIN Trial Balance'[Year End] = "Aug",
                         IF(VALUES('FIN Trial Balance'[Year End]) = "Aug",
                             DATESYTD('Calendar DateTable'[Calendar Date], "08-31"),
                             DATESYTD('Calendar DateTable'[Calendar Date], "08-31")),
                         IF('FIN Trial Balance'[Year End] = "Dec",
                         IF(VALUES('FIN Trial Balance'[Year End]) = "Dec",
                             DATESYTD('Calendar DateTable'[Calendar Date], "12-31"),
                             DATESYTD('Calendar DateTable'[Calendar Date], "12-31")),
                         IF('FIN Trial Balance'[Year End] = "Jun",
                         IF(VALUES('FIN Trial Balance'[Year End]) = "Jun",
                             DATESYTD('Calendar DateTable'[Calendar Date], "06-30"),
                             DATESYTD('Calendar DateTable'[Calendar Date], "06-30")
                         )
          )
     )

    Friday, December 1, 2017 4:12 AM
  • Sorry, I failed to notice that you'd modified the code incorrectly to include the Apr year end twice. And I was only using IF because I thought we were just dealing with 2 choices Apr or Dec.

    If we have to deal with multiple choices it would be easier to use the SWITCH statement. Although I believe that switch can only return scalar values not tables so we'd have to push the calculate() down into the result expression.

    eg.

    =IF(HASONEVALUE('FIN Trial Balance'[Year End]),
        SWITCH(VALUES('FIN Trial Balance'[Year End])
            ,"Apr", CALCULATE([Final Figures],
                          ActualBudget[Actual/Budget] = "Actual",
                          DATESYTD('Calendar DateTable'[Calendar Date], "04-30"))
            ,"Aug", CALCULATE([Final Figures],
                          ActualBudget[Actual/Budget] = "Actual",
                          DATESYTD('Calendar DateTable'[Calendar Date], "08-31"))
            ,"Dec", CALCULATE([Final Figures],
                          ActualBudget[Actual/Budget] = "Actual",
                          DATESYTD('Calendar DateTable'[Calendar Date], "12-31"))
            ,"Jun", CALCULATE([Final Figures],
                          ActualBudget[Actual/Budget] = "Actual",
                          DATESYTD('Calendar DateTable'[Calendar Date], "06-30"))
        )
    )


    http://darren.gosbell.com - please mark correct answers


    Friday, December 1, 2017 4:46 AM
  • Thanks Darren!!!

    Was able to 'modify' my table a little, since if I were to use only 'FIN Trial Balance'[Year End], I only get partially correct [Final Figures], since other factTables 'were not considered'.

    My last question would be that, for 'BUDGET', how would I incorporate the formula to have it know exactly from which beginning month of the year to which ending month of the year to take.

    For example, in Dec Year End, Budget would be from Jan 2017 to Dec 2017.

    For Apr Year End, However, it'll be taking May 2017 to Apr 2018.

    Currently, I have my Budget DAX formula to be:

    =CALCULATE([Final Figures],
                         ActualBudget[Actual/Budget] = "Budget",
                         FILTER(ALL('Calendar DateTable'),
                         'Calendar DateTable'[Year]= MAX('Calendar DateTable'[Year]))
    )

    What I'd like to have AFTER incorporating your formula would be:

    =

    IF(HASONEVALUE(tblCommunity[Year End]),
           SWITCH(VALUES(tblCommunity[Year End])
           ,
           "Apr", CALCULATE([Final Figures],
                                         ActualBudget[Actual/Budget] = "Budget",
                                         FILTER(ALL('Calendar DateTable'),
                                         'Calendar DateTable'[Calendar Date] >= DATE(MAX('Calendar DateTable'[Year]), 5, 1) &&  'Calendar DateTable'[Calendar Date] <= DATE(MAX('Calendar DateTable'[Year]) + 1, 4, 30))) <-- This Calendar Part doesn't work
          ,
           "Dec", CALCULATE([Final Figures],
                                         ActualBudget[Actual/Budget] = "Budget",
                                         FILTER(ALL('Calendar DateTable'),
                                         'Calendar DateTable'[Year]= MAX('Calendar DateTable'[Year])))
         )
    )
    • Edited by dluhut Friday, December 1, 2017 5:26 PM
    Friday, December 1, 2017 5:17 PM
  • Was able to 'modify' my table a little, since if I were to use only 'FIN Trial Balance'[Year End], I only get partially correct [Final Figures], since other factTables 'were not considered'.

    Yes, I can't remember if I mentioned this earlier or not, but if you have multiple facts you would most likely be better of having a Company table and putting the YearEnd column in there so that it can be referenced centrally.

    If you want to get the fully year range for the budget you would be better off using the STARTOFYEAR and ENDOFYEAR functions (which take the same parameters as DATESYTD) to specify the date range filters. i'm not sure if that MAX based approach will work in all scenarios.


    http://darren.gosbell.com - please mark correct answers

    Friday, December 1, 2017 9:31 PM
  • If you want to get the fully year range for the budget you would be better off using the STARTOFYEAR and ENDOFYEAR functions (which take the same parameters as DATESYTD) to specify the date range filters. i'm not sure if that MAX based approach will work in all scenarios.


    http://darren.gosbell.com - please mark correct answers

    Tried STARTOFYEAR and ENDOFYEAR but isn't correct. Below is just calculate if it's Apr Year End

    =CALCULATE([Final Figures],
                         ActualBudget[Actual/Budget] = "Budget",
                         FILTER(ALL('Calendar DateTable'),
                         STARTOFYEAR('Calendar DateTable'[Calendar Date], "05-01-2017") &&
                         ENDOFYEAR('Calendar DateTable'[Calendar Date], "04-30-2018")))

    What is it that I missed here?

    Also, I was also couldn't understand, why is it that the formula below doesn't work, as if it's a Dec Year End it works, but when I change it to "look" at the 'Fiscal DateTable', it doesn't work.

    CALCULATE([Final Figures],
                                         ActualBudget[Actual/Budget] = "Budget",
                                         FILTER(ALL('Calendar DateTable'),
                                         'Fiscal DateTable'[Year]= MAX('Calendar DateTable'[Year])))


    • Edited by dluhut Friday, December 1, 2017 10:15 PM
    Friday, December 1, 2017 10:00 PM
  • Any helper!!!

    Been stuck with this for 2 days and still couldn't figure out.

    Monday, December 4, 2017 5:06 AM
  • What is it that I missed here?

    As I mentioned in my previous reply, you call the startofyear / endofyear functions with the same parameters as DATESYTD. ie. by passing in a date column and the END of the financial year. And the simplest approach would be to use the DATESBETWEEN function. What you are doing at the moment looks like it's just filtering twice for the single date of April 30th. The following example should give you the full year value for an April 30 year end.

    =CALCULATE([Final Figures],
                         ActualBudget[Actual/Budget] = "Budget",
                         DATESBETWEEN(
                         STARTOFYEAR('Calendar DateTable'[Calendar Date], "04-30"), 
                         ENDOFYEAR('Calendar DateTable'[Calendar Date], "04-30")))


    http://darren.gosbell.com - please mark correct answers

    Monday, December 4, 2017 6:42 AM
  • =CALCULATE([Final Figures],

                         ActualBudget[Actual/Budget] = "Budget",
                         DATESBETWEEN(
                         STARTOFYEAR('Calendar DateTable'[Calendar Date], "04-30"), 
                         ENDOFYEAR('Calendar DateTable'[Calendar Date], "04-30")))


    http://darren.gosbell.com - please mark correct answers

    Going with an example that you gave me here, I got an error message indicating that DATESBETWEEN need a minimum of 3 parameters.

    Thus below is what I've added.

    =CALCULATE([Final Figures],

                         ActualBudget[Actual/Budget] = "Budget",
                         DATESBETWEEN(
                         STARTOFYEAR('Calendar DateTable'[Calendar Date], "04-30"),   <---is this supposed to be "05-01"?
                         ENDOFYEAR('Calendar DateTable'[Calendar Date], "04-30")))

    However, it still return the wrong result. I am wondering if it's because the slicers that user choose (Oct for example), is 'included' in the calculation. Thus, I added the "original FILTER(ALL('Calendar DateTable')...' and it gave me the following error message.

    =CALCULATE([Final Figures],

                         ActualBudget[Actual/Budget] = "Budget",
                        FILTER(ALL('Calendar DateTable'),
                               DATESBETWEEN(('Calendar DateTable'[Calendar Date],
                                          STARTOFYEAR('Calendar DateTable'[Calendar Date], "04-30"), 
                                          ENDOFYEAR('Calendar DateTable'[Calendar Date], "04-30"))))


    "Calculation error in measure 'FIN Trial Balance[Budget]: A table of multiple values was supplied where a single value was expected"

    What's wrong!!!??? At this point, how I wished DAX could be like Excel 'Evaluate Formula', where I can step through each step and see what are the values that it's picking

    Monday, December 4, 2017 3:34 PM
  • I just missed off the date column as the first parameter

    =CALCULATE([Final Figures],

                         ActualBudget[Actual/Budget] = "Budget",
                         DATESBETWEEN(                     'Calendar DateTable'[Calendar Date],
                         STARTOFYEAR('Calendar DateTable'[Calendar Date], "04-30"),  
                         ENDOFYEAR('Calendar DateTable'[Calendar Date], "04-30")))


    http://darren.gosbell.com - please mark correct answers

    Monday, December 4, 2017 7:13 PM
  • I just missed off the date column as the first parameter

    =CALCULATE([Final Figures],

                         ActualBudget[Actual/Budget] = "Budget",
                         DATESBETWEEN(                     'Calendar DateTable'[Calendar Date],
                         STARTOFYEAR('Calendar DateTable'[Calendar Date], "04-30"),  
                         ENDOFYEAR('Calendar DateTable'[Calendar Date], "04-30")))


    http://darren.gosbell.com - please mark correct answers

    That's the correction that I did, and still I didn't get the correct result for my Budget.

    But it works for the 'YTD' though (although it uses DATESYTD and not DATESBETWEEN with STARTOFYEAR and ENDOFYEAR). Below is the DAX formula to get the correct result using DATESYTD.

    CALCULATE([Final Figures], DATESYTD('Calendar DateTable'[Calendar Date], "04-30"), ActualBudget=[Actual/Budget] = "Actual").

    If it works for 'Actual' using DATESYTD, and since you said that DATESBETWEEN with STARTOFYEAR and ENDOFYEAR combination works the same as DATESYTD, so why is it that it's not giving me the correct value? And interestingly enough, when I double click on the value that spits out for me, so that I can see the 'list' of data that sums up to the value that it gave me, I was only given 2 rows of data, and these 2 rows of data DID NOT SUM UP to the value of that was shown in the pivot table. Thus I wasn't even able to check what data were being filtered to achieve the incorrect value in the pivot.

    • Edited by dluhut Tuesday, December 5, 2017 10:38 PM
    Tuesday, December 5, 2017 2:42 PM