DAX measure - two value types - two date ranges

[Lilliua]

Hi all!

I have two tables that i need to work on: Orders and Calender.

The Orders table has following columns: Product (text), Quantity (number), OrderDate (date), Price (number), Seller (text), Discounts (number).

*The Product column has three values: Red, Blue, Yellow.

The Calender table has following columns: Date (whole date format), Month, MonthName, Quarter, Year, DayofMonth.

* The date range is 01.01.2015-05.31.2019.

I have already a measure for:

- [TotalSales]

- [Red] = CALCULATE ([TotalSales], FILTER ('Orders', 'Orders' [Product]="Red"))

- same as above for Blue and Yellow

I need to create a visual with a column chart that will show at the same time:

- All data for Red from the start date till 30.04.2017

- All data for Blue from 01.05.2018 till end date

So i need to filter a column in a table by a text value (red/blue) and filter it then by a given date range. I was trying to use DATESBETWEEN, but as I am new to this I guess I miss something here. I tried to do it for Red and Blue separately first:

Red = CALCULATE((Orders[Product]="Red")),  DATESBETWEEN(Calender[Date], DATE(01.01.2015), DATE(30.04.2017)).

Could you help out here? Would appreciate it much, thanks.

[Anonymous · Accepted solution]

Hi @Lilliua ,

You can create a measure as below to get it, please find the details in the attachment.

Measure = 
VAR _product =
    SELECTEDVALUE ( 'Orders'[Product] )
RETURN
    SWITCH (
        _product,
        "Red",
            SUMX (
                FILTER (
                    'Orders',
                    'Orders'[OrderDate] >= DATE ( 2015, 1, 1 )
                        && 'Orders'[OrderDate] <= DATE ( 2017, 4, 30 )
                ),
                [TotalSales]
            ),
        "Blue",
            SUMX (
                FILTER (
                    'Orders',
                    'Orders'[OrderDate] >= DATE ( 2018, 5, 1 )
                        && 'Orders'[OrderDate] <= DATE ( 2019, 5, 31 )
                ),
                [TotalSales]
            )
    )

Best Regards