Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Date Functions- in measures

Hello, 

 

I have created some measures to look at the count of deliveries in the last 3 months/last 6 months / last 12 months.

So if a delivery is from 2 months ago, it will be included in all three measures.

 

 

I want to now be able to replicate these measures, but instead of looking at the last 6 months, I want to see deliveries between the last 3-6 months, and between the last 6-12 months.

So that the deliveries are split into buckets of 0-3, 3-6 & 6-12, without being double counted in any.

 

Can someone show me how this can be done in a similar way to the measures I have already? I do not want to create any calculated columns.

 

 

Many Thanks

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    Please try to update the formula of measure [] and check whether you can get the desired result:

    DeliveryItems_Last 03-06 Months =
    VAR datestart =
        CALCULATE (
            DATEADD ( 'Calendar'[Date], -3, MONTH ),
            ALL ( 'Calendar' ),
            'Calendar'[Is Current Day] = TRUE ()
        )
    VAR datesend =
        CALCULATE (
            DATEADD ( 'Calendar'[Date], -6, MONTH ),
            ALL ( 'Calendar' ),
            'Calendar'[Is Current Day] = TRUE ()
        )
    RETURN
        CALCULATE (
            COUNT ( 'Transportation Cost'[MaterialKey] ),
            Material[Material Group HL] = "Laminate"
                || Material[Material Group HL] = "Vinyl"
                || Material[Material Group HL] = "Wood",
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Date] >= datestart
                    && 'Calendar'[Date] <= datesend
            ),
            'Customer Sales'[Customer Group] <> "99"
                && 'Customer Sales'[Customer Group] <> "90"
                && 'Customer Sales'[Customer Group] <> "91"
        )

    If the above one is not working in your scenario, please provide some sample data(exclude sensitive data) and your expected result with examples. By the way, what's the data type of field  'Calendar'[Date short]? Its data type is Date or some else type? Thank you.

    Best Regards

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Make your datetoday  formula the same as date fromcal and subtract 3 months when you need 3-6

     

    You can also try like

    examples

    Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date]),0),-3,MONTH))
    Rolling 3 before 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date]),-3),-3,MONTH))

     

    Rolling 6 before 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date]),-6),-6,MONTH))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak ,

       

      I tried to replicate as you advised:

       

       

      But I get the following error message about comparing Date & Text values,

       

      What do I need to adjust to clear this message?

       

      Many Thanks

       

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        Anonymous , Use these calculation to get min and max date 

         

        var _max1 = maxx(allselected('Date1'), 'Date1'[Date])
        var _max = eomonth(_max1,-3)
        var _min = eomonth(_max1,-6)+1

         

         

        or

         
        var _max1 = today()
        var _max = eomonth(_max1,-3)
        var _min = eomonth(_max1,-6)+1

         

        make sure filter is applied on date