Group values
Hi everyone, I would like to create a regroupment of KPI in different "bloc" for cleaning the analyse of dashboard. I mean, one bloc for - "N year" with KPI "Semaine" + " N date" - On other "N-1 year" with "MEC N-1" + "N-1 date" etc. - Etc. The aim is to compartiment for a good view. I tried many ways but it didn't work.. Your help is precious. Thank you in advance
Last non blank month with context issues
Hi! I have issue with below measure. It works when there are no additional columns that would "break down" the Actual/Plan/Forecast measure. But as soon as there is such column together in the matrix with below, the values are wrong. I've noticed it's because the NO(ISBLANK()) gets evaluated separately for every "category" it's broken down into, instead on the whole measure. Below is the representation what the measure does. . Year Period Job Category Raw Amount LastNonBlankPeriod 2023 11 Finance 5 11 2023 11 Sales 10 12 2023 12 Finance BLANK() 11 2023 12 Sales 11 12 Because Finance is BLANK in 2023, it takes period 11 into account for this specific category. Where my goal is it should always be 12 in below case as there are some values for period 12. And my values from below measure, if I remove period split will be: Finance 5, Sales 11. Instead of Finance 0, Sales 11 How can I fix the measure? Headcount Switch Yearly = SUMX( VALUES('X_Calendar Period'[F_YEAR]), VAR CurrentYear = 'X_Calendar Period'[F_YEAR] VAR LastActualsPeriod = CALCULATE( MAX('X_Calendar Period'[F_YEAR_MONTH_NO]), FILTER( ALL('X_Calendar Period'), 'X_Calendar Period'[F_YEAR] = CurrentYear && NOT(ISBLANK([Headcount By Heads])) ) ) VAR LastForecastPeriod = CALCULATE( MAX('X_Calendar Period'[F_YEAR_MONTH_NO]), FILTER( ALL('X_Calendar Period'), 'X_Calendar Period'[F_YEAR] = CurrentYear && NOT(ISBLANK([FCST Headcount By Heads])) ) ) VAR LastPlanPeriod = CALCULATE( MAX('X_Calendar Period'[F_YEAR_MONTH_NO]), FILTER( ALL('X_Calendar Period'), 'X_Calendar Period'[F_YEAR] = CurrentYear && NOT(ISBLANK([PLAN Headcount By Heads])) ) ) RETURN SWITCH( TRUE(), CurrentYear <= [Actuals Year], CALCULATE([Headcount By Heads], 'X_Calendar Period'[F_YEAR_MONTH_NO] = LastActualsPeriod), CurrentYear = [Forecast Year], CALCULATE([FCST Headcount By Heads], 'X_Calendar Period'[F_YEAR_MONTH_NO] = LastForecastPeriod), CurrentYear = [Plan Year], CALCULATE([PLAN Headcount By Heads], 'X_Calendar Period'[F_YEAR_MONTH_NO] = LastPlanPeriod), BLANK() ) )Filter context doesnt apply with custom date slicer
Hello everyone, I have implemented a new Date Picker following this quick tutorial in order to extend the possibility of filtering my data. However, I am struggling with calculating measures for the previous period selected in my Date Picker. First, here is how my model is built: here is the measure I want to calculate in a modified filter context: here is a quick example of how my Dateslicer selection is built: I then put the "Period" column in a slicer in order to select the "Last calendar week" option and then filter the Datedim table in the corresponding manner and to replace the classic between date slicer. However, since I have implemented this configuration, I am not able to calculate the previous_period_measure with this original formula (startOfPrevPeriod and endOfPrevPeriod being out of the data range filtered with the slicer) : Previously, without the DateSlicerSelection, the DateDim[FullDateAlternateKey] was in a between date slicer and everything was working fine. Despite the fact that I have absolutely no idea of how to fix it, I have made few observations: I am able to get the very first Date of my DimDate table even with it is filtered with he following formula : Modifying my measure calculation with the following formula, I got the result for the current period selection and not the previous one: Please, does someone has an idea on how to fix this problem ? Best Regards, Melvin
Night shift (logic, calculation)
Hi everybody, I'm looking for a solution, but I can't find it. I would like to know the time worked. Info: - People work in the morning, afternoon and night. Overtime is also possible. - Thousands of posts are made in a day, that's thousands of time data. So there is not only a beginning and an end. - Morning shift: 06-14 (can be overtime: 02-14, 06-18) - Afternoon shift: 14-22 (can be overtime: 10-22, 14-02) - Night shift: 22-06 (can be overtime: 18-06, 22-10) MIN - MAX works in the morning and in the afternoon, because the time data comes one after the other. It does not work at night because it is separate, e.g.: 0:00 (start) -> (end) 6:00 + 22:00 (start) -> (end) 0:00. This is a MIN - MAX 24h. time worked = VAR balans = CALCULATE ( MAX (EVENTHistory[hour]), FILTER ( ALLSELECTED ( 'EVENTHistory' ), EARLIER (EVENTHistory[worker]) = 'EVENTHistory'[worker] ) ) - CALCULATE ( MIN (EVENTHistory[hour]), FILTER ( ALLSELECTED ( 'EVENTHistory' ), EARLIER (EVENTHistory[worker]) = 'EVENTHistory'[worker] ) ) RETURN FORMAT ( INT ( balans * 24 ), "00" ) & ":" & FORMAT ( INT ( MINUTE ( balans ) ), "00" ) & ":" & FORMAT ( INT ( SECOND ( balans ) ), "00" ) Thank you in advance for your help.
