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 thr...
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

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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    amitchandak thanks for your help,

    I am still struggling to determine where to add in the max/min date calculations, can you indicate where I should add these if I paste in the DAX text below?

     

    DeliveryItems_Last 03-06 Months =
    VAR datestart =
    CALCULATE (
    DATEADD ( 'Calendar'[Date], -3, MONTH ),
    ALL ( 'Calendar' ),
    'Calendar'[Is Current Day] = TRUE ()
    )
    VAR datefromcalc =
    CALCULATE (
    DATEADD ( 'Calendar'[Date], -6, MONTH ),
    ALL ( 'Calendar' ),
    'Calendar'[Is Current Day] = TRUE ()
    )
    VAR datefrom =
    CALCULATE (
    MAX ( 'Calendar'[Date short] ),
    ALL ( 'Calendar' ),
    'Calendar'[Date] = datefromcalc
    )
    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 short] <= datestart
    && 'Calendar'[Date short] >= datefrom
    ),
    'Customer Sales'[Customer Group] <> "99" && 'Customer Sales'[Customer Group] <> "90" && 'Customer Sales'[Customer Group] <> "91")
     
     
    Many Thanks
    • Anonymous's avatar
      Anonymous
      Not applicable
      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