FILTER A VALUE DEPENDING ON THE DATE OF ANOTHER TABLE

[rahulrajeev312]

I have a table where sales data is getting updated per item-wise, I have another table where I had updated the sales taget.

My aim is to apply that filter to this table and when I filter the sales table with date the target also should get filtered.

The below shown is the table I created for sales target per person :

Column 1 - Salseman code

Column 2 - Target Per Day

Column 3 - Dates from 2020 to 2023

How can I do it, I tried to build a relationship between these two tables but it's not working. Please help me with this.

If you need more details on this please let me know.

[v-luwang-msft]

Hi @rahulrajeev312 ，

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[amitchandak]

@rahulrajeev312 , You should create a common date table, join it with the date of both tables and filter those. If needed month/year of the target can be converted into start or end date or month/year

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[rahulrajeev312]

@amitchandak But the problem is the sales table will not have date of all days, for example, If no sale is happening today it will not show today's date.

Also how can I join it , which tool I can use for that.

[amitchandak]

@rahulrajeev312 , Once you create a date tbale, What dates are there on not does not matter, date table will have dates. You can create formula based on date table date

Date Table

Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])

)

refer if needed

Power BI for Beginners - DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s
#PowerBI #DAX #Calendar # #powerquery #Index #AmitChandakDA #businessintelligence #DA100 #powerplatform #bi #powerbidesktop #datatodiscovery #powerbitutorial

https://www.youtube.com/c/Amitchandak?sub_confirmation=1
Power BI for Beginners - Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...

[rahulrajeev312]

@amitchandak Please help...