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

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