Date Functions- in measures

[ACUNILIN]

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

[amitchandak]

@ACUNILIN , 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))

[ACUNILIN]

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]

@ACUNILIN , 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 

[ACUNILIN]

@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

[v-yiruan-msft · Accepted solution]

Hi @ACUNILIN ,

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