Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need help with a scenario with the headcount, where the numbers given under Opening is picked as is at monthly level but when its quarterly level we need to maintain the Jan data as Opening and Closing headcount to be last month of the period.
Ex - Q1 -> Opening will be of Jan and Closing numbers will be of Mar.
Below is the DAX used by me which sums up when selecting Q1, instead i need the above logic to work. Sample excel file attached.
F_OpeningHC =
var yr = SELECTEDVALUE(Dim_time[Year])
var ml = SELECTEDVALUE(Dim_time[MonthLoad])
Return
CALCULATE(SUM(sf_CSS[hc_opening]),sf_CSS[sf_year]=yr,sf_CSS[sf_month_load]=ml)
Looking for the help to build this logic. @tamerj1
Solved! Go to Solution.
Hi @nareshr89
Apologies for the late reply. I was out of office all day.
Please try by replacing SELECTEDVALUE ( Dim_time[MonthLoad] ) with MIN ( Dim_time[MonthLoad] ) for Opening HC and MAX ( Dim_time[MonthLoad] ) for Closing HC
Thanks for reverting @tamerj1.
Found the solution using similar logic for the YearMonth ID.
F_OpeningHC =
var yr = SELECTEDVALUE(Dim_time[Year])
var ml = SELECTEDVALUE(Dim_time[MonthLoad])
VAR yearmonth = CALCULATE(MIN(sf_CSS[time_id]),sf_CSS[sf_year]=yr,sf_CSS[sf_month_load]=ml)
Return
CALCULATE(SUM(sf_CSS[hc_opening]),sf_CSS[sf_year]=yr,sf_CSS[sf_month_load]=ml,sf_CSS[time_id]= yearmonth)
F_ClosingHC =
var yr = SELECTEDVALUE(Dim_time[Year])
var ml = SELECTEDVALUE(Dim_time[MonthLoad])
VAR yearmonth = CALCULATE(MAX(sf_CSS[time_id]),sf_CSS[sf_year]=yr,sf_CSS[sf_month_load]=ml)
Return
CALCULATE(SUM(sf_CSS[hc_closing]),sf_CSS[sf_year]=yr,sf_CSS[sf_month_load]=ml,sf_CSS[time_id]=yearmonth)
Thanks for reverting @tamerj1.
Found the solution using similar logic for the YearMonth ID.
F_OpeningHC =
var yr = SELECTEDVALUE(Dim_time[Year])
var ml = SELECTEDVALUE(Dim_time[MonthLoad])
VAR yearmonth = CALCULATE(MIN(sf_CSS[time_id]),sf_CSS[sf_year]=yr,sf_CSS[sf_month_load]=ml)
Return
CALCULATE(SUM(sf_CSS[hc_opening]),sf_CSS[sf_year]=yr,sf_CSS[sf_month_load]=ml,sf_CSS[time_id]= yearmonth)
F_ClosingHC =
var yr = SELECTEDVALUE(Dim_time[Year])
var ml = SELECTEDVALUE(Dim_time[MonthLoad])
VAR yearmonth = CALCULATE(MAX(sf_CSS[time_id]),sf_CSS[sf_year]=yr,sf_CSS[sf_month_load]=ml)
Return
CALCULATE(SUM(sf_CSS[hc_closing]),sf_CSS[sf_year]=yr,sf_CSS[sf_month_load]=ml,sf_CSS[time_id]=yearmonth)
Hi @nareshr89
Apologies for the late reply. I was out of office all day.
Please try by replacing SELECTEDVALUE ( Dim_time[MonthLoad] ) with MIN ( Dim_time[MonthLoad] ) for Opening HC and MAX ( Dim_time[MonthLoad] ) for Closing HC
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
26 | |
22 | |
11 | |
8 |
User | Count |
---|---|
75 | |
50 | |
47 | |
17 | |
17 |