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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors