Forum Discussion

lasmithfla's avatar
lasmithfla
Icon for Helper I rankHelper I
4 years ago

Need help with a Monthly value to repeat when showing data by week

The starting day of week is from my calendar table.  My Headcount source is only monthly but I need to show data by week.

I have a Month/year in both sources that can be used to join, it's an inactive join because the active join is on the actual date (for other reports tied to this source).

As you can see, my calculation is not correct and summing all the months and not repeating just that matching month.

So in the view below, for example for 1st 4 lines, i want the value to be the 1871.  Next month, i want the value to be the 1894.

 
 
HC Weekly = VAR _HCWeekly = SELECTEDVALUE('Calendar'[Starting Day of Week])
Return
CALCULATE(Sum(HeadcountPSWDSummary[HeadCount]),
Filter(ALLSELECTED(HeadcountPSWDSummary),
Year(HeadcountPSWDSummary[Data Date] = Year(_HCWeekly) && Month (HeadcountPSWDSummary[Data Date] = _HCWeekly))))

 

I had tried a 2nd calc that gives the same values showing in the Headcount column

HC Weekly 2 = CALCULATE([Total HC],USERELATIONSHIP('Calendar'[Year Month],HeadcountPSWDSummary[Year Month]))

 

Need Help