Working days

DaemonCross — Wed, 06 Sep 2023 22:20:36 GMT

Get the accumulated result of the previous month based on working days.

I'm working on a project where I'm going to compare the current month against several other months that have values recorded.
But this comparison cannot be the current date against the same date in the previous month, it has to be the same number of working days in the current month against the same number of working days in other months.

I've already created a calendar table where each month has an index column to add the working days (for example: August = 23; April = 19).

The code that worked for me to be able to get the exact amount accumulated up to the date equal to the current month:

Pegar o valor acumulado do mês passado baseado em dias úteis.

Estou trabalhando em um projeto onde vou comparar o mês atual contra os diversos outros meses que possuírem registro de valores. Mas essa comparação não pode ser da data atual contra a mesma data no mês anterior, ela tem que ser mesma quantidade de dias úteis do mês atual contra a mesma quantidade de dias úteis dos outros meses.

Eu já criei uma tabela calendário onde cada mês possui uma coluna índice para somar os dias que forem úteis (por exemplo: agosto = 23; abril = 19).

O código que funcionou comigo para poder pegar o valor exato acumulado até a data igual a do mês atual:

02_Justo_cancelamento = 

var vDate_Max = 

CALCULATE(
    MAX(D_Calendario[Data_ref]);
    REMOVEFILTERS()
)

var vDay_Date_Max = DAY(vDate_Max)
var vMonth_Date_Max = MONTH(vDate_Max)
var vYear_Date_Max = YEAR(vDate_Max)

var vContext_Date = MAX(D_Calendario[Data_ref])
var vContext_Month = MONTH(vContext_Date)
var vContext_Year = YEAR(vContext_Date)

var vResult_last_month_diff_jan = 
CALCULATE([Cancel];
    FILTER( 
        ALL(D_Calendario[Data_ref]); 
        D_Calendario[Data_ref] >= DATE(vContext_Year; vContext_Month - 2; 1) &&
        D_Calendario[Data_ref] <= DATE(vContext_Year; vContext_Month - 2; vDay_Date_Max)))

var vResult_last_month_equal_jan = 
CALCULATE([Cancel];
    FILTER( 
        ALL(D_Calendario[Data_ref]); 
        D_Calendario[Data_ref] >= DATE(vContext_Year - 1; 11; 1) &&
        D_Calendario[Data_ref] <= DATE(vContext_Year - 1; 11; vDay_Date_Max)))

var vResult_last_month = 
IF( vMonth_Date_Max = 1;
    vResult_last_month_equal_jan;
    vResult_last_month_diff_jan)

var vLast_Month = 
CALCULATE( [Cancel];
    DATEADD(D_Calendario[Data_ref]; -1; MONTH))

var vFinal_Result = 

IF( vContext_Month = vMonth_Date_Max && vContext_Year = vYear_Date_Max;
    vResult_last_month;
    vLast_Month)

return
vFinal_Result

Re: Working days

lbendlin — Sun, 10 Sep 2023 18:13:20 GMT

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

topic Working days in DAX Commands and Tips

Working days

Re: Working days