Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

DAX YTD and LYTD Limitations

  • 1.  DAX YTD and LYTD Limitations

    Gold Contributor
    Posted Aug 17, 2019 10:57 AM
      |   view attached
    I'm hoping someone can help me confirm the limitations of the YTD and LYTD functions. I'm looking to report on my YTD and LYTD but exactly until today, not until the end of the month which I see in most posts or videos. I've attached a PBIX file for reference. My test data shows that my YTD for the fiscal year of July 1 2019 - June 30 2020 should be a total of 2 and my LYTD from July 1 2018 to today's date August 18 2019 should be 7 which is in the red rectangle below.

    However when I create my DAX measure for LYTD I'm getting a result of 11 which is for the entire year. I feel that I might be using the wrong DAX to try to achieve this. So far, my measures are

    Sum of Sales = SUM(Sales[Sales Amount])
    YTD = TOTALYTD([Sum of Sales], 'Calendar'[Date], "06/30")
    LYTD = CALCULATE([YTD], DATEADD ('Calendar'[Date], -1, YEAR))

    Is it possible to get accuracy to the day with the LYTD? If so what formula should I use?

    Thanks in advance





    ------------------------------
    Daniel Demers
    Chief Digital Information Officer
    Dan
    61466718572
    ------------------------------

    Attachment(s)

    pbix
    Test File 2.pbix   151K 1 version


  • 2.  RE: DAX YTD and LYTD Limitations

    Gold Contributor
    Posted Aug 18, 2019 01:38 PM
    Edited by Simon Lamb Aug 18, 2019 01:41 PM
    Hi @Daniel Demers,

    Your DAX is fine.  If you throw your measures into a table with Calendar[Date] you should see them calculating just as you expect, with LYTD showing 7 today.

    YTD calculations need a TO date, which in the table is provided by the [Date] field.  In the absence of a TO date it will default to the last date in the calendar.  Your Card visual has no date field, hence the result you're seeing.

    If you add Calendar[Date] to the visual filter for the Card, select 'Relative date filtering' and then 'Show items when the value is in the last 1 days [sic], including today' then all will be good.  Same applies to your YTD card.

    Regards,

    ------------------------------
    Simon Lamb
    IT Consultant, Method Excel Ltd.
    ------------------------------



  • 3.  RE: DAX YTD and LYTD Limitations

    Bronze Contributor
    Posted 30 days ago
      |   view attached
    Hi @Daniel Demers
    The other way to handle this:
    1. limit your Calendar table up to CurrentDate (or the latest date in your Sales table).
    2. add LASTDATE to your LYTD measure:

    LYTD = CALCULATE([YTD],

        DATEADD(LASTDATE('CALENDAR'[Date]),-1,YEAR))

    ​see the attached .pbix
    Best,
    Pawel


    ------------------------------
    Pawel Jaszczynski
    Power BI Consultant & Trainer
    Excellab
    +436781255548
    ------------------------------

    Attachment(s)

    pbix
    LYTD up to date.pbix   158K 1 version


  • 4.  RE: DAX YTD and LYTD Limitations

    Top Contributor
    Posted 30 days ago
      |   view attached
    Hi @Daniel Demers

    Please see if the following helps you in the current set-up in your sample file.
    ​The above is achieved through:

    1. Added a custom column in the date table to indicate whether the day is before the current day:
    Is Less Than Current Day = 
    IF ( 'Calendar'[Date] < TODAY (), TRUE (), FALSE () )​
    2. Changed the LYTD DAX a bit:
    LYTD New = 
    CALCULATE (
        [YTD],
        CALCULATETABLE (
            DATEADD ( 'Calendar'[Date], -1, YEAR ),
            'Calendar'[Is Less Than Current Day] = TRUE ()
        )
    )​

    PFA the pbix file also for your reference.

    ------------------------------
    Gopa Kumar
    Limner Consulting
    ------------------------------

    Attachment(s)