Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Data Between Custom Week Start and Week End Date

In my report i have created Week Start and Week End date and for my organization the Week Start Date is Monday and End Date is Sunday,  in the table above we have only data until 3/24/2019, but ...
Need Help
Tips and Tricks
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous 

    Just adjust the measure formula as below:

    Measure3 = 
VAR __max = CALCULATE(MAX('Table'[Week End Date]),FILTER('Table','Table'[Week End Date]<TODAY()))
RETURN
SUMX(FILTER('Table',[Week End Date] = __max),[Count])

    You could use Current Day instead of TODAY()

     

    Best Regards,

    Lin

Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

Seems like you could grap the MAX of the "My WeekEndDate" column and then FILTER down to that row essentially. So, something like:

 

Measure = 
VAR __max = MAX([My WeekEndDate])
RETURN
SUMX(FILTER('Table',[My WeekEndDate] = __max),[Ticket Count CFY])
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thank your for the response, Yes that makes sense when we don't have data for the Current week,Let me explain againIn the below example i have data for Week Ending 31 March as well and in this scenario if i run the report on 28th March 2019 i should see only data for 3/18/2019-3/24/2019 i.e. 5, the Data for 03/25/2019 to 03/31/2019 should only be reflected in the report when i run refresh the data on 04/01/2019.

    • v-lili6-msft's avatar
      v-lili6-msft
      Community Support
      7 years ago

      hi, Anonymous 

      Just adjust the measure formula as below:

      Measure3 = 
VAR __max = CALCULATE(MAX('Table'[Week End Date]),FILTER('Table','Table'[Week End Date]<TODAY()))
RETURN
SUMX(FILTER('Table',[Week End Date] = __max),[Count])

      You could use Current Day instead of TODAY()

       

      Best Regards,

      Lin