Forum Discussion

Lennard's avatar
Lennard
Frequent Visitor
3 years ago
Solved

selectedvalue doesnt work with date-aggregations

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's avatar
    Lennard
    3 years ago

    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

3 Replies

  • Lennard's avatar
    Lennard
    Frequent Visitor
    3 years ago

    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
  • TomasAndersson's avatar
    TomasAndersson
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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])
)

     

     

  • Lennard's avatar
    Lennard
    Frequent Visitor
    3 years ago

    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)