Forum Discussion

davidi4524's avatar
davidi4524
Helper III
9 years ago
Solved

how to create sum by dates column

hi ' i have two tables: 1. sales per date columns: branch,date,sale 2.target per date columns: branch,date,target   i need to calculate the the target - total sales per date my problem is that t...
Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    davidi4524,

     

    You can use the DAX expression below to get your result.
    Sales table:
    Column = LOOKUPVALUE(Target[Target],Target[Date],Sales[Date])

    Target table

    Column = CALCULATE(SUM(Sales[Sales]),FILTER(ALL(Sales),Sales[Date]=Target[Date]))

     

    Regards,

    Charlie Liao

davidi4524's avatar
davidi4524
Helper III
9 years ago

but for the target i dont need to do sum , i just need to show it.

and how can i do target-sales?

 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Even if you have a single entry of a single value, you still need to use a formula that will handle aggregate data.  Power BI, ahead of time, doesn't know you will only ever have 1 value.  By telling it to sum, or average or whatever, you are instructing it on how to handle 1 or more rows of data.