Forum Discussion

WishAskedSooner's avatar
WishAskedSooner
Continued Contributor
1 year ago
Solved

YTD Chart Not Working Properly

I am playing around in the Adventure Works PBIX. One scenario I am testing is when the dates don't align exactly in the fact table. For example, I edited the fact table and deleted all the rows for B...
Need Help
  • parry2k's avatar
    parry2k
    1 year ago

    WishAskedSooner try something like this, replace table and column names as per your model

     

    YTD Last Transaction Date = 
VAR __LastTransactionDate = CALCULATE ( MAX ( Sales[OrderDate] ), ALL () )
RETURN
CALCULATE (
    [Sales],
    DATESYTD ( 
        CALCULATETABLE (
            VALUES ( 'Calendar'[Date] ),
            KEEPFILTERS (
                'Calendar'[Date] <= __LastTransactionDate
            )
        )
    )
)
parry2k's avatar
parry2k
Super User
1 year ago

WishAskedSooner try something like this, replace table and column names as per your model

 

YTD Last Transaction Date = 
VAR __LastTransactionDate = CALCULATE ( MAX ( Sales[OrderDate] ), ALL () )
RETURN
CALCULATE (
    [Sales],
    DATESYTD ( 
        CALCULATETABLE (
            VALUES ( 'Calendar'[Date] ),
            KEEPFILTERS (
                'Calendar'[Date] <= __LastTransactionDate
            )
        )
    )
)
  • WishAskedSooner's avatar
    WishAskedSooner
    Continued Contributor
    1 year ago

    parry2k , thank you so very much for your thoughtful reply. I am marking it as a solution. I had to make some edits to get it working properly in my model. Here is the final measure that I came up with using your example:

     

    Sales_YTD_2 =
        VAR _LatestOrderDateKey = MAX('Sales'[OrderDateKey])
        RETURN
            CALCULATE(SUM('Sales'[Sales Amount]), DATESYTD(CALCULATETABLE(VALUES('Date'[Date]), 'Date'[DateKey] <= _LatestOrderDateKey)))
     
    Some things I don't quite understand.
    - I could not use the LastOrderDateKey measure that I created in the DAX above. I had to define a VAR inside the measure. I find this behavior odd.
    - Furthermore, if I forgoe creating a VAR and just stick the MAX('Sales'[OrderDateKey]) in the CALCULATETABLE function, it loses the filter-context even if I wrap it with KEEPFILTERS like your example.
     
    Don't get me wrong. I am super happy that it works if I define the VAR and use like above. But, there are some aspects of DAX that I simply do not understand.
     
    As for the dynamic filter on Year, I will probably make a separate post on that.
     
    Thanks!