DAX PowerBI SUM from the beginning up to current date (input by user)

0
votes

I have 4 tables Calendar, Products, Region, and Sales. Then I create the relationship between 4 tables through

  • Region.Region_code -> Sales.Region_Code
  • Product.Product_wid -> Sales.Product_wid
  • Calendar.Cal_Row_wid -> Sales.Date_wid

Then I create 2 slicers Date and Month:

enter image description here

I would like to write a measure to calculate the Total quantity_rcs (which belongs to table Sales) for all order from the beginning up to current date (which characterized by Date and Month; both of them belong to Calendars).

1
powerbidax

1 Answers

1
votes

How this should work if you have multivalue slicer?

belove example for onevalue slicer where we are pointing to specific date (consider that you have a unique date column In calendar maybe "issue_date"):

Measure = 
var __date =  calculate(max(Calendar[issue_date]), FILTER(ALL(Calendar[Date] 
,Calendar[Month],Calendar[Year]),
Calendar[Date] = SELECTEDVALUE(Calendar[Date]) &&
Calendar[Month] = SELECTEDVALUE(Calendar[Month]) &&
Calendar[Year] = YEAR(TODAY())
)
)
return

calculate( sum(Sales[quantity_rcs]), 
    FILTER(ALL(Calendar[issue_date]), 
        Calendar[issue_date] <= __date )
   )