Average Sales= VAR TotalSales = 'Fact Sales'[Total Sales] VAR NoOfDays = IF(ISCROSSFILTERED('Dim Date'[DateKey]),SUM('DimDate'[No of Days in Fact Sales]), 1) VAR NoOfDaysNormalized = CALCULATE(SUM('DimDate'[No of Days in Fact Sales]),TREATAS(VALUES('Dim Normalized Sales'[Month Key]),'DimDate'[Date key])) VAR AvgsalesNormalized = DIVIDE([TotalSalesNormalized], NoOfDaysNormalized, 0) VAR AvgSales = DIVIDE(TotalSales, NoOfDays, 0) RETURN IF(ISCROSSFILTERED('Dim Normalized Date'[Normalized DAte]),AvgSalesNormalized,AvgSales)
We are seeing issue with Treatas here, The goal here is Avg sales should work fine when ever we drag Date and NormalizedSalesDate drag Normalized date. Currently seeing issue when ever we drag NormalizedSalesDate ,
Ideally it should get distinct dates from Dim Normalized Sales Date Table and filter date table and date table should filter Fact Table.
Please assit how to write query like this in DAX
SELECT SUM(SALES) FROM Factsales where MonthKey in (
Select distinct date key from DimDate where DateKey in (select distint DateKey from DimNormalizedDate))
NotE: we don't have bidirecitonal relationship between Dim Date and Dim Normalized Sales
@abhiram342 , Can you share sample data and sample output in a table format? Or a sample pbix after removing sensitive data.
Try a measure like
measure = var _tab = distinct(DimNormalizedDate[DateKey]) var _date = distinct(filter(DimDate, DimDate[DateKey] in _tab), DimDate[MonthKey]) return calculate(sum(Sales[SALES]), filter(Sales,Sales[MonthKey] in _date))