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 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

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.

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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'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.

         

  • Irwan's avatar
    Irwan
    Super User
    2 years ago

    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.