Forum Discussion

Slorp's avatar
Slorp
New Member
2 years ago

Filter dates

Hi all.

I'm looking for a way to solve this. I have in my model 3 key tables 1) a dates catalog with a column named "month" 2) a table called "service" with several registers and one of the columns in it is called "Invoiced Service Date" and 3) a table called sales with several registers aswell and a column calles "Sold Date".

Both "Sold Date" and "Invoiced Service Date" are related (connected) to the column "month" on dates catalog.

I have a filter with "month" were you can pick an specific month i.e December.

I need to figure out how can I count how many distinct registers are in "Invoiced Service Date" from the month picked and five months back (i.e Dec Nov Oct Sep Aug and Jul) and count how many distinct registers are in "Sold Date" from the remaining previous months (i.e Jun May Apr Mar Feb and Jan).

In summary I want to achieve this:

Example

FILTER: Dec 23

"Service Invoice Date" registers: from Dec 23 to Jul 23

"Sold Date" registers: from Jun 23 to Jan 23

 

And if I pick another month on the filter, it adjust the date periods

Example

NEW FILTER PICKED: Nov 23

"Service Invoice Date" registers: from Nov 23 to Jun 23

 

"Sold Date" registers: from May 23 to Dec 22.

1 Reply

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

    To achieve the desired result, you can use DAX (Data Analysis Expressions) in Power BI, assuming you are working with Power BI based on the context. Here is a step-by-step guide on how to create the measures:

    1. Create Date Relationships: Make sure you have established the relationships between your "dates catalog" table and the "service" and "sales" tables based on the "month" column.

    2. Create Measures: Create two measures, one for "Service Invoice Date" and one for "Sold Date."

    Service Invoice Date Count =
    CALCULATE(
    DISTINCTCOUNT('service'[YourPrimaryKeyColumn]),
    'dates catalog'[month] >= MAX('dates catalog'[month]) - 5 &&
    'dates catalog'[month] <= MAX('dates catalog'[month])
    )

     

     

    Sold Date Count =
    CALCULATE(
    DISTINCTCOUNT('sales'[YourPrimaryKeyColumn]),
    'dates catalog'[month] >= MAX('dates catalog'[month]) - 11 &&
    'dates catalog'[month] <= MAX('dates catalog'[month]) - 6
    )

     

    1. Replace 'service'[YourPrimaryKeyColumn] and 'sales'[YourPrimaryKeyColumn] with the actual primary key columns in your "service" and "sales" tables.

    2. Use Measures in Visuals: Now, you can use these measures in your Power BI visuals. When you change the filter on the "dates catalog" table for a specific month, these measures will automatically adjust the date periods accordingly.

    Remember to adjust the relationships and column names based on your actual data model. The key idea here is to use the CALCULATE function along with the date conditions to filter the data based on the selected month and the previous months.