Need help on DAX calculation

[lavankumar1989a]

Hi Experts,

Looking for your support on creating a calcuated column to show the products information by apply sertain filters through dax.

When it comes to my requirment, i have a table like below with sample data

i am joining the above table with my date table by date to date. As per user requirement if user selected a date range, based on that we need to show the count of products along with the respective product values should show like below,

If user selected date range as 01-01-2023 to 31-12-2023, then i need to show the details for unsold products for the month on Nov'2023.

When means unsold count of products: 3 and products info should be Bike & Bus.

product count in a card visual and products info should be on table visual

This should be dynamic based on the user selection of date. So pls do the needful on getting this done and share the sample .pbix if you achive this . Thank you so much for your much appreciated support!

Thanks,

Lavan

[v-yajiewan-msft]

Hi @lavankumar1989a ，Thanks for the reply from @Irwan , please allow me to provide another insight:  

• Create a date table for your necessary if you do not have it:

DateTable = CALENDAR(DATE (2023, 1, 1), DATE (2023, 12, 31))

• Ensure there's a relationship between your product table and the date table based on the date fields.

• Since you need this to be dynamic based on user selection, we will use two measures instead of calculated columns：

UnSoldCount = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Date] IN ALLSELECTED(DateTable[Date]) && 'Table'[Status]="unsold"))

UnsoldProduct =

CONCATENATEX(

    FILTER(

        'Table',

        'Table'[Date] IN ALLSELECTED(DateTable[Date]) && 'Table'[Status] = "unsold"

    ),

    'Table'[Product], 

    ", "

)

As irwan mentioned, the "Auto" product will also be included in your resut,if I misundertand your issue, please feel free to reply:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lavankumar1989a]

Thanks @v-yajiewan-msft  and @Irwan  for you kind reply.

Let me brief my requirement that what i am looking,

I have a 2 tables 1.fact table & 2. date dimension table and relationship established between fact & date dimension tables using date key or date.

i have a slicer as date range starting from 01-01-2021 to 30-06-2024, when user select the data range as like below,

01-01-2023 to 31-12-2023, first we find the month for max date range which means it's dec'2023 month of max(31-12-2023). based on this we need to show product info as max month-1 means Nov'2023 unslod information need to show in table visual.

if user select time range as 20-06-2023  to 30-11-2023, max month would be Nov'2023 and need to show previous month data from max date selected. which means Oct'2023 unsold if any. this should be dynamic in nature.

hope it's clear, please do let me know if any clarifications needed. Thank you so much for your efforts and help!

[v-yajiewan-msft · Accepted solution]

Hi @lavankumar1989a, thank you for your additional inforamtion!
Please check the udpdated answer:

UnSoldCount = 
VAR SelectedStartDate = MIN('DateTable'[Date])
VAR SelectedEndDate = MAX('DateTable'[Date])
VAR AdjustedEndDate = EOMONTH(SelectedEndDate, -1)
RETURN
CALCULATE(
   COUNTROWS('Table'),
   FILTER(
       'Table',
       'Table'[Date] >= SelectedStartDate &&
       'Table'[Date] <= AdjustedEndDate &&
       'Table'[Status] = "unsold"
   ))

UnSoldProduct = 
VAR SelectedStartDate = MIN('DateTable'[Date])
VAR SelectedEndDate = MAX('DateTable'[Date])
VAR AdjustedEndDate = EOMONTH(SelectedEndDate, -1)
RETURN
CONCATENATEX(
    FILTER(
        'Table',
        'Table'[Date] >= SelectedStartDate &&
        'Table'[Date] <= AdjustedEndDate &&
        'Table'[Status] = "unsold"
    ),
    'Table'[Product],
    ", "
)

Result for your reference:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lavankumar1989a]

Thank you so much @v-yajiewan-msft and much appriceated dor your support. DAX that you shared is working as expected.

[Irwan]

hello @lavankumar1989a 

please check if this accomodate your need.

as far as i know, there are two ways to show your goal. 

1. you can use measure

- create a new measure

Unsold = COUNTROWS(FILTER('Table','Table'[Status]="Unsold"))

- plot your data in table and/or card visual

2. you can use visual filter

- plot you data in table then tick "Unsold" using visual filter for showing only Unsold

Also I find your result is weird, you said theresult should be Bike and Bus, but there is one Auto with Unsold status on 25-12-2023.

Hope this will help you.

Thank you.