Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Need help

I have Append-QMR table in power bi. i have TOUR_REQUEST_NO and WEEK_NO and INQUIRY_DATE_KEY and TERMINATION_YEAR columns in that table. the INQUIRY_DATE_KEY is related with FULL_DATE column in M_INQ_DATE_D date table. I want to distinctcount of TOUR_REQUEST_NO created between the dates in this this week by taking refence INQUIRY_DATE_KEY .
and should showed in line chart a week number in X axis and Count in Y axis ..and My count should be present in between week days of present week..
this is my model

I want to extract the dates from date table by ENQUIRT_DATE_KEY and calculate distinct tour request numbers between this week days.

4 Replies

  • 123abc's avatar
    123abc
    Community Champion
    2 years ago

    To achieve your goal in Power BI, you can follow these steps:

    1. Create a Calendar Table: Ensure you have a calendar table that includes a date column (FULL_DATE in your case) which links to the INQUIRY_DATE_KEY in your main table.

    2. Create Measures:

      • First, create a measure that calculates the distinct count of TOUR_REQUEST_NO for the current week. This measure will use the relationship between your main table and the date table.
      • Then, create another measure to filter the count to only include the current week days.

    3. Create a Line Chart:

      • Use the week number from your calendar table on the X-axis.
      • Use the distinct count measure on the Y-axis.

    Here's how you can create the measures:

    1. Distinct Count of TOUR_REQUEST_NO for the Current Week:

     

    Tour Requests This Week =
    CALCULATE (
    DISTINCTCOUNT ( 'Append-QMR'[TOUR_REQUEST_NO] ),
    DATESBETWEEN (
    'M_INQ_DATE_D'[FULL_DATE],
    FIRSTDATE ( 'M_INQ_DATE_D'[FULL_DATE] ),
    LASTDATE ( 'M_INQ_DATE_D'[FULL_DATE] )
    )
    )

     

    Distinct Count of TOUR_REQUEST_NO for Current Week Days:

     

    Tour Requests This Week Days =
    CALCULATE (
    [Tour Requests This Week],
    FILTER (
    ALLSELECTED ( 'M_INQ_DATE_D'[FULL_DATE] ),
    WEEKDAY ( 'M_INQ_DATE_D'[FULL_DATE] ) <> 1
    && WEEKDAY ( 'M_INQ_DATE_D'[FULL_DATE] ) <> 7
    )
    )

     

    In the second measure, the FILTER function ensures that only the weekdays (Monday to Friday) are considered. Adjust the weekday numbers according to your locale if needed.

    Now, you can create a line chart:

    • Drag the week number from your calendar table to the X-axis.
    • Use the Tour Requests This Week Days measure for the Y-axis.

    This chart will display the count of distinct tour request numbers for the weekdays of the current week over the week numbers. Adjust your visualizations as needed based on your data and preferences.

     

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

     

    In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      This week is not completed right..So fro example the last week dates are'BETWEEN '2024-02-05' and '2024-02-11'. and I want to get how many Count created from last week to this week difference. How can I get ..

      "select COUNT( DISTINCT TOUR_REQUEST_NO) from s_spl_qmr_do_weekly_2024_f join m_date_d on INQUIRY_DATE_KEY = date_key where WEEK_NO =7 and FULL_DATE BETWEEN '2024-02-05' and '2024-02-11' "
      I have build this Query in sql..please convert this into PBI DAX function

      It is exactly logic

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      It is showing blank value for both the measures...
      my Question is ...I have Append-QMR table in power bi . in that I have TOUR_REQUEST_NO and FULL_DATE and WEEK_NO columns . I need to calculate DISTICTCOUNT of TOUR_REQUEST_NO for each week individually between the dates which are falls in a week from FULL_DATE column. for example for 7th week falls between the dates '2024-02-11 to 2024-02-18 ', for that week the count should be between these dates like this .it should be showed in line chart like as WEEK_NO in X-Axis and No.Of counts in Y-axis.

  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    Community Support
    2 years ago

    Hi Anonymous ,

     

    If your problem is still not resolved, can you share sample data and the results you are hoping for? Show it as a screenshot or excel. Or a sample pbix after removing sensitive data. We can better understand the problem and help you.


    Best Regards,
    Yulia Yan


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