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() ) )
Dynamic Format not working with Durations
In my reports, I use durations in seconds for various metrics. It is handy to create visuals and make calculations, but when displaying the values, users prefer to see "hh:mm:ss" instead of the number of seconds. I convert the durations to part-of-day decimal and use FORMAT() to display it as "hh:mm:ss" or "nn:ss" if less than an hour. But this does not work with dynamic formating: although i return a text string containing the appropriate format, it is handled as text instead of being applied to the decimal value as format stirng. So, how can i use dynamic formatting to apply the appropriate format string to a duration? The .pbix demonstrates the issue with a simple example (it's in weTransfer, can't upload here yet): https://we.tl/t-gdqaD1nf3d Thank you, Nikos
ALL() leads to wrong calculations
Hello all, I am experiencing some problems with the ALL() function. In a few words, I am want to display the total sales + market shares for a few item from field 1. - To do so, I am filtering my table to display only the wanted items. - Also, in the DAX formula for my Market Share, I used the ALL() function for the denominator to take into account all the items in field 1, and not only the one filtered. Here it is Denominator = CALCULATE ( SUM ( TABLE[Sales] ), ALL ( TABLE[field 1] ) ) => However, I noticed that this calculation is lower than it should be, as if Power BI was not taking into account all the lines in my table. => Randomly, I added an item in my table (which accounts for a large number of lines in my TABLE). Then the calculation was correct as if this forced Power BI to take into account all the lines in my TABLE when calculating. I looked through the Microsoft documentation (https://docs.microsoft.com/fr-fr/dax/all-function-dax), it did not help since it does not seem to be a problem of "Dax auto-exist' (https://www.sqlbi.com/articles/understanding-dax-auto-exist/). Many thanks for your help !!
