Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Help modifying a query

I am currently using a query that pulls data from a server for a selected date range. I am trying to edit it to pull three years worth of data in the following manner:

1-1-2019 thru 4-4-2019

1-1-2020 thru 4-4-2020

1-1-2021 thru 4-4-2021

I tried duplicating and editing  the date portion , but that only gets me a token identifier error. Do I need to duplicate the entire statement three times? Hoping someone can help.  Her is the date portion of the query- (d.timedown between '2019-01-01' and '2019-4-4')"]),
    

dates
Help Requested
query

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Are you trying to filter in DAX or while getting data from SQL ?

     

    In DAX you can have measure like

     

    YTD Corrected =
    var _max = format(today(),"MMDD")
    return
    calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),format('Date'[Date],"MMDD")<=_max)

     

    or


    YTD Corrected =
    var _max = format(today(),"MMDD")
    return
    if(format(max('Date'[Date]),"MMDD")<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])),blank())

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for responding, I am pulling from SQL (I think). I am not very familiar with DAX. When I look at your  YTD samples, where would I enter the actual date range, and Would I repeat this command three times? Sorry for my inexperience, and thank you for you patience!

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.