Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
DaemonCross
New Member

Working days

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
1 REPLY 1
lbendlin
Super User
Super User

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-...
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/1447...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors