I have three Dimension tables (Dates, Months and Stores) and one Fact table (Store Costs).
[Dates} table has the below columns:
-- Date
--Day (1 to 31) as per the month.
[Months} has
--[Month]
[Generic Date]
[Date]
[Month]
[Generic Date]
[Stores} table has the below columns:
--Store Code
--Store Name
[Store Costs] has the below columns:
[Generic Date] -- all the values are based on month
[Cost of Labor]
[Expenses]
[Store Code]
[Sales]
[Generic Date] -- all the values are based on date
--[Generic Date]
-- Sales
--Cost of Sales
--Store Code
I designed a table to show the below value:
[Store Name]
[Sales MTD]
[Cost of Labor MTD]
[Expenses MTD}
[Cost of Labor MTD} = [Cost of Labor] x ( $(vDaysCountMTD) / $(vDaysCountCM)]
vDaysCountMTD formula is as below:
If(getselectedcount([Month Year])<>1,
Count({$<[Date] = {"<=$(=Date(Today()-1))"}, [YearMonth No]= ,[Year] = ,[Month No]=,[Month Year]=,[Month]=,[MonthID]= {"$(=((Year(Today()) -1)*12) + Num(Month(Today())))"}>} Distinct [Date Key])
,
Count({$<[Date] = {"<=$(=Date(Today()-1))"}, [YearMonth No]= ,[Year] = ,[Month No]=,[MonthID]=,[Month]=,[Month Year]= {"$(=getfieldselections([Month Year]))"}>} Distinct [Date Key])
)
vDaysCountCM =
If(getselectedcount([Month Year])<>1,
Count({$<[YearMonth No]= ,[Year] = ,[Month No]=,[Month Year]=,[Month]=,[MonthID]= {"$(=((Year(Today()) -1)*12) + Num(Month(Today())))"}>} Distinct [Date Key])
,
Count({$<[YearMonth No]= ,[Year] = ,[Month No]=,[MonthID]=,[Month]=,[Month Year]= {"$(=getfieldselections([Month Year]))"}>} Distinct [Date Key])
)
I i run the dashboard the calculations were wrong. For some of the stores the vDaysCountCM gives 15 for some 31 and for some 10.
It should give 31 for all since the month has 31 days.
For some stores the formula brings nothing.
what I understands that it count on the Dates in the Sales table instead of the [Date] table.
.
.
