Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create a rolling date measure for date

I have created a measure for a date field 

Week_CurrentTransCount =
(
VAR _Cuur_start = Max('Date'[Current Date])
Var _Rolling_5_Days = CALCULATE(SUM(Final[count]),DATESINPERIOD(DATE(Max('Date'[Current Date])),Max('Date'[Current Date]),-5,DAY)
VAR _Curr_END = Max('Date'[Current Date])
return
calculate(SUM(Final[count]),Final[Date] >=_Rolling_5_Days && Final[Date] <= _Curr_END ))
 
My objective is when user selects a date range then this measure will show values of a week.
e.g if user selects 1-1-2020 to 30-1-2020 then this measure will show values for the date 24-1-2020 to 30-1-2020
 
 
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • az38's avatar
    az38
    6 years ago

    Anonymous 

    it looks like you have incorrect syntax.

    try

    CurrentTransCount =
(
VAR _Cuur_start = Min('Date'[Current Date])
VAR _Curr_END = Max('Date'[Current Date])
return
calculate(SUM(Final[TransCountnUm]), Final[Date] >= _Cuur_start, Final[Date] <= _Curr_END )

    or

    CurrentTransCount =
(
VAR _Cuur_start = Min('Date'[Current Date])
VAR _Curr_END = Max('Date'[Current Date])
return
calculate(SUM(Final[TransCountnUm]),FILTER(ALL(FInal),Final[Date] >= _Cuur_start && Final[Date] <= _Curr_END ))

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    I would just do:

     

    Week_CurrentTransCount =
VAR _Curr_start = Max('Date'[Current Date])
VAR _Curr_END = (_Curr_start - 5)
return
SUMX(FILTER(Final,Final[Date] >= _Curr_start && Final[Date] <= _Curr_END ),Final[count])
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I am not able to get any data in my measure.Although it is correct syntax wise .

      The measures i used for first 2 columns are working 

      CurrentTransCount =
      (
      VAR _Cuur_start = Min('Date'[Current Date])
      VAR _Curr_END = Max('Date'[Current Date])
      return
      calculate(SUM(Final[TransCountnUm]),Final[Date] >= _Cuur_start && Final[Date] <= _Curr_END ))

       

      • az38's avatar
        az38
        Community Champion
        6 years ago

        Anonymous 

        it looks like you have incorrect syntax.

        try

        CurrentTransCount =
(
VAR _Cuur_start = Min('Date'[Current Date])
VAR _Curr_END = Max('Date'[Current Date])
return
calculate(SUM(Final[TransCountnUm]), Final[Date] >= _Cuur_start, Final[Date] <= _Curr_END )

        or

        CurrentTransCount =
(
VAR _Cuur_start = Min('Date'[Current Date])
VAR _Curr_END = Max('Date'[Current Date])
return
calculate(SUM(Final[TransCountnUm]),FILTER(ALL(FInal),Final[Date] >= _Cuur_start && Final[Date] <= _Curr_END ))
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try like

    Week_CurrentTransCount =
(
VAR _Max = MaxX('Date', 'Date'[Current Date])
VAR _Min = _Max -7
return
calculate(SUM(Final[count]),filter(all('Date'),'Date'[Current Date] >=_Min && 'Date'[Current Date] <= _Max ))