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], DATESBETWEEN([Date table], [min date], [max date]))

(not attempted the Saturday part yet)

but this returns the aggregate of the measures for all rows. I need the actual measure for each row.

Could someone assist?

thanks

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.

4 Replies

  • 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])