Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Return values between 2 dates, for Saturdays only

Hi I have a set of measures and a min and max date defined. I need the measures to only show between these dates, and only where the day is a Saturday. I have tried.. CALCULATE([measure], DATESBETW...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    Try to create a measure like this to calculate the min date:

    min date =
MINX (
    FILTER ( ALL ( 'datetable' ), 'datetable'[Date] <> BLANK () ),
    'datetable'[Date]
)

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

timg's avatar
timg
Icon for Solution Sage rankSolution Sage
5 years ago

Hi gbii,

 

Maybe I'm thinking outside of the box a bit but perhaps your reporting needs would be fulfilled with a calculated column? The folowing formula will add a column in which all Saturday dates will be marked as "saturday". You could use this new filter/slicer to modify all your measures to only show saturday related data (image 1 for reference)

 

 

Ind_SaturDay =
IF ( WEEKDAY ( DimDate[Date], 2 ) = 6, "Saturday", "Other" )

 

 

  image 1

An alternative, if you want the logic completely within the measure could be something like this:

TestMeasure =
CALCULATE (
    [Revenue],
    FILTER ( DimDate, DimDate[Date] > DATE ( 2018, 01, 01 ) ),
    FILTER ( DimDate, DimDate[Date] <= DATE ( 2019, 12, 31 ) ),
    FILTER ( DimDate, WEEKDAY ( DimDate[Date], 2 ) = 6 )
)

 

Would either of those solve the issue with the Saturday filter in your case?

 

Best regards,

 

Tim

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks timg. Still not working unfortunately. The dates that I am inputting are measures (first being a MIN date, second a MAX) as they have to be dynamic. Wondering if this is having an effect..?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    It works when I hardcode the date as you have done timg. However my dates are predefined as MIN and MAX as follows. I think it's because I have stamped them on every row. How do I get the MIN date for example to stamp on 1 row only?

    MIN DATE = FIRSTNONBLANK(ALL([Date Table]), [measure])

    • v-yingjl's avatar
      v-yingjl
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi Anonymous ,

      Try to create a measure like this to calculate the min date:

      min date =
MINX (
    FILTER ( ALL ( 'datetable' ), 'datetable'[Date] <> BLANK () ),
    'datetable'[Date]
)

       

      Best Regards,
      Community Support Team _ Yingjie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.