selectedvalue doesnt work with date-aggregations

[Lennard]

Hey there, 

I have problems to solve the following task:

Ich have 2 Data Tables 

1) --> TargetSalesPerDay for each Project

2) --> individual Project Start / End-Date

I managed to get the following result using this DAX: 

_TargetSales =

CALCULATE([_TargetSalesPerDay],

filter(ProjectAppointment,SELECTEDVALUE('Calendar'[Date])>=ProjectAppointment[Start]),

filter(ProjectAppointment,SELECTEDVALUE('Calendar'[Date])<ProjectAppointment[End])

)

Problem: With this solution I cannot aggregate targetSales to CalendarWeeks, Months, Years, ...

If I try the visualization blanks 

 Does somebody have an idea how to fix my DAX? 

Best regards,

Lennard

My TestDataset can be downloaded here: https://www.dropbox.com/scl/fo/3l9ea9yn9yuv9ouzesp1d/h?dl=0&rlkey=7iozb3d95q4g133lte4kznqb2

[Lennard · Accepted solution]

Solution: I worked it out with a vitual table: 

Target_table=

    filter(

        filter(

            filter(

                CROSSJOIN(Kalendar,ProjectAppointment,Project),

            Project[SalesProjectID]=ProjectAppointment[SalesProject]),

        Kalendar[Tag]>=ProjectAppointment[Start]),

    Kalendar[Tag]<ProjectAppointment[End])

and the related measure: 

Final_target = sum(Target_table[TargetSalesPerDay])

Now it works!

Best regards

[Lennard]

Hey @TomasAndersson , 

thank you very much so far. I really appreciate it. I got one step closer to the solution. 

But your solution doesnt offer the right aggregation right now. My expectation for May 2022: 

300 Sales per Day over 31 Days --> 9.300 Sales (to be shown in the visualization) 

[TomasAndersson]

Hi!
Since there's no longer a unique value when using years etc. SELECTEDVALUE() might not work. You should be able to use the following instead (you might have to adjust to match your table and column names):

_TargetSales = 
CALCULATE(sum('Table'[_TargetSalesPerDay]),
    filter(ALLSELECTED(ProjectAppointment),
        max('Dates'[Date])>=ProjectAppointment[Start] &&
        max('Dates'[Date])<ProjectAppointment[End])
)