locked
How to add date filters RRS feed

  • Question

  • Hello. I have a measure that I am placing into powerpivot with years. How can I make it so that ALL the years show but for the current year it only shows up to a certain date? Should I do that with date filters or in the measure formula itself? 
    Monday, September 12, 2016 4:27 PM

Answers

  • Hi Albo44,

    >>How can I make it so that ALL the years show but for the current year it only shows up to a certain date? Should I do that with date filters or in the measure formula itself?

    Could you please post your measure for further analysis? Or please give us more details and sample data structure?

    On the one hand, you can create a measure using the functions in following examples:
    DATESYTD Function returns a table that contains a column of the dates for the year to date, in the current context.
    DATESINPERIOD function returns a table that contains a column of dates that begins with the start_date and continues for the specified intervals.

    YTD total Cost:=CALCULATE(SUM([sale]),DATESYTD(Cost[date])) 

    This measure will calculate the total sum of current year even through you fact table is not overall year.

    SaleInperiod:=CALCULATE(SUM(Cost[sale]),DATESINPERIOD(Cost[date],MAX(Cost[date]),-1*8,MONTH)) 

    You can replace the number ‘8’ to special period you want.

    On the other hand, you can set the Date as filter, so you can get the expected results.

    Regards,
    Angelia


    Wednesday, September 14, 2016 9:08 AM

All replies

  • Hello,

    Not clear for me what you mean, can you post an example, please?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, September 12, 2016 4:47 PM
  • Hi Albo44,

    >>How can I make it so that ALL the years show but for the current year it only shows up to a certain date? Should I do that with date filters or in the measure formula itself?

    Could you please post your measure for further analysis? Or please give us more details and sample data structure?

    On the one hand, you can create a measure using the functions in following examples:
    DATESYTD Function returns a table that contains a column of the dates for the year to date, in the current context.
    DATESINPERIOD function returns a table that contains a column of dates that begins with the start_date and continues for the specified intervals.

    YTD total Cost:=CALCULATE(SUM([sale]),DATESYTD(Cost[date])) 

    This measure will calculate the total sum of current year even through you fact table is not overall year.

    SaleInperiod:=CALCULATE(SUM(Cost[sale]),DATESINPERIOD(Cost[date],MAX(Cost[date]),-1*8,MONTH)) 

    You can replace the number ‘8’ to special period you want.

    On the other hand, you can set the Date as filter, so you can get the expected results.

    Regards,
    Angelia


    Wednesday, September 14, 2016 9:08 AM