Daily Revenue+Target month tables => Calculate target with DAYLY SLICER date

[Adam_Shelton]

Hi Everyone!!!

I have this issue, please give me some advices:

I have two tables:

 1. Daily Sales order Revenue includes: order id, date (DAILY), product shop information,... revenue

 2. Monthly target table: Year, month, target value (MONTHLY), shop ID, mm-yyyy (01-2022), Frist day of month, day end of month (2 end column that i created)

I wanna create a DAX to calculate Target value and apply date daily slicer. For instance:

Target value 01.2022: 3000, 02.2022: 4000

 - Case 1: Choose slicer between 01.01.2022 to 29.01.2022 (days in Jan). Target should be fixed: 3000

 - Case 2: Choose slicer between 01.01.2022 to 05.02.2022 (days in both jan and feb): Target value should be fixed: 7000 (3000+4000)

Please give me some advices, thanks a lot

Link file PBI

https://drive.google.com/file/d/1GL_yUEaU8hYnVnATnXLhrBXQJRrUQpH_/view?usp=sharing

[tamerj1]

Hi @Adam_Shelton 
Please refer to attached sample file for more details. First you need to create a new calculated column in the date table as follows

Then you need to edit the relationship as follows

Start of Month = 
CALCULATE ( 
     MIN ( 'Date'[Date] ),
     ALLEXCEPT ( 'Date', 'Date'[CalendarYear],'Date'[months] )
)

[Adam_Shelton · Accepted solution]

Hi @tamerj1!

When i created relationship, i got this:

But thanks for your adivce, i got an idea and it worked. I create a month table - a table with only year and month by using dax: 

Create relationship between 3 tables: date, month table, target. Change cross filter direction to both and I got what i want 😆

Again, thank you a lot! 👍

[tamerj1]

@Adam_Shelton 

Happy for you despite that it deosn't make alot of sense. Most importantly, it works good for you.