Forum Discussion

GiangLe's avatar
GiangLe
Icon for Helper I rankHelper I
8 years ago
Solved

Year to date without standard date table

Hi everyone,

I've struggled with this problem for few days already and would be glad if someone can help me out :).
I'm working a dataset without a standard date column (as you can see in the photo) and i would like to achieve YTD sales in the third column. (i know that i can create a date table and map that one with this table, i've tried that but still the already available YTD dax function didnt work)

Thanks

 

Need Help
  • mattbrice's avatar
    mattbrice
    8 years ago
    YTD Measure :=
CALCULATE (
    SUM ( Table[Sales] ),
    FILTER (
        ALL ( Table[Yearmonth] ),
        Table[Yearmonth] <= MAX ( Table[Yearmonth] )
    )
)

     But if measure needs to cross "Years", you'll need a separate column for Year and add to filter statement. 

     

    Or do like suggested and convert your Yearmonth column to a date, then use time intelligence.

4 Replies

  • corbusier's avatar
    corbusier
    Icon for Advocate IV rankAdvocate IV
    8 years ago

    Have you tried using LEFT() and RIGHT() combined with DATE() to make a date out of the yearmonth field? This should then enable you to use the regular time intelligence functions.

  • mattbrice's avatar
    mattbrice
    Icon for Solution Sage rankSolution Sage
    8 years ago
    YTD Measure :=
CALCULATE (
    SUM ( Table[Sales] ),
    FILTER (
        ALL ( Table[Yearmonth] ),
        Table[Yearmonth] <= MAX ( Table[Yearmonth] )
    )
)

     But if measure needs to cross "Years", you'll need a separate column for Year and add to filter statement. 

     

    Or do like suggested and convert your Yearmonth column to a date, then use time intelligence.

    • GiangLe's avatar
      GiangLe
      Icon for Helper I rankHelper I
      8 years ago

      Thanks! I've tried your method and it worked like a charm in my sample data but somehow acted weird in my real dataset. I'm looking deeper into this, but i think this is the way to go.

    • GiangLe's avatar
      GiangLe
      Icon for Helper I rankHelper I
      8 years ago

      Apparently, i now fully understand what you said ealier :). I'd like to add year into my filter too so i could calculate cross year. However, none of those dax i've tried work. Could you help?