Forum Discussion

lavankumar1989a's avatar
lavankumar1989a
Helper II
2 years ago
Solved

Need help on DAX calculation

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 bel...
Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
lavankumar1989a
Helper II
2 years ago

Thanks Anonymous  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!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    lavankumar1989a
    Helper II
    2 years ago

    Thank you so much Anonymous and much appriceated dor your support. DAX that you shared is working as expected.