Forum Discussion
Help with DAX - Count Using two coluns Date as Filter
So, I have a data set that is compose of a List of Tasks to do that can be recurrent.
I want to evry task that is Conclud, and that is program to end this month so I try:
Concluded Tasks = CALCULATE(
COUNT(Tasks[Task]),
FILTER(
ALL('Calendar'),
'Calendar'[Mes] = MAX('Calendar'[Mes]) &&
'Calendar'[Ano] = MAX('Calendar'[Ano])
),
FILTER(
Tasks,
Tasks[Conclusion Date] <= MAX('Calendar'[Data_id])
)
)
but I get this:
when I was expecting something like this (not exactly this but close):
All Dates ar in yyyyMMdd in the table Tasks.
More Information:
My Tables are:
Calendar[Data_id] 1 -> * Tasks[Expected Date] (active)
Calendar[Data_id] 1 -> * Tasks[Conclusion Date] (inactive)
Calendar[Data_id] 1 -> * Tasks[Reference Date] (inactive)
Calendar[Data_id] 1 -> * Tasks[Reference Date] (active, just for filter)
Source code table Task:
let
Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZA9C4MwEIb/imR2uA+T0rWFglt3cchgp9SC2v9fFfVCSAIWArnhnvd5uaZRd9fZvkBVqmn4dvNHQDA/fYxY7SMCqrZMMSwMC0NZpgJzjMiSlGO0eDReJCnHmJWRBH+X/ugUMn4nSHQKGeMxdI1344yHMO4JGc9jSIeex2d422k92Mu6Ua6AOjZuVN2Pk3XF81aH3JK7LzMkbHTOtlF8gmp/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, Recurrent = _t, #"Expected Date" = _t, #"Conclusion Date" = _t, #"Reference Date" = _t]),
#"Valor Substituído" = Table.ReplaceValue(Fonte,"",null,Replacer.ReplaceValue,{"Expected Date", "Conclusion Date", "Reference Date"}),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Valor Substituído",{{"Task", type text}, {"Recurrent", type logical}, {"Expected Date", type text}, {"Conclusion Date", type text}, {"Reference Date", type text}})
in
#"Tipo Alterado"
Source code table Calendar:
let
Fonte = List.Dates(#date(2020,1,1), 365, #duration(1,0,0,0)),
#"Convertido para Tabela" = Table.FromList(Fonte, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Colunas Renomeadas" = Table.RenameColumns(#"Convertido para Tabela",{{"Column1", "Data"}}),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Colunas Renomeadas",{{"Data", type date}}),
#"Personalização Adicionada" = Table.AddColumn(#"Tipo Alterado", "Data_id", each Date.ToText([Data], "yyyyMMdd"), type text),
#"Ano Inserido" = Table.AddColumn(#"Personalização Adicionada", "Ano", each Date.Year([Data]), Int64.Type),
#"Mês Inserido" = Table.AddColumn(#"Ano Inserido", "Mes", each Date.Month([Data]), Int64.Type),
#"Semana do Mês Inserida" = Table.AddColumn(#"Mês Inserido", "Semana do Mês", each Date.WeekOfMonth([Data]), Int64.Type),
#"Nome do Mês Inserido" = Table.AddColumn(#"Semana do Mês Inserida", "Nome do Mês", each Date.MonthName([Data]), type text)
in
#"Nome do Mês Inserido"
DAX formula table Compet:
Compet = 'Calendar'
DAX Running Total Expected Tasks by Month:
Expected Tasks = CALCULATE(
COUNT(Tasks[Task]),
FILTER(
ALL('Calendar'),
'Calendar'[Mes] = MAX('Calendar'[Mes]) &&
'Calendar'[Ano] = MAX('Calendar'[Ano])
)
)
**EDIT**
Add a second image that does not load the 1st time.
**EDIT 2**
Add a second image that does not load the 2nd time.
**EDIT 3**
Add a second image that does not load the 3rd time.
danielpcamara , Not very clear, what you want to achieve. But the file attached after signature and HR Blog should help you https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
2 Replies
- amitchandakSuper User
danielpcamara , Not very clear, what you want to achieve. But the file attached after signature and HR Blog should help you https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
- danielpcamaraResolver I
Thank you amitchandak. The "Current Employees" was what I Needed.
I solve with this:
Concluded Tasks = IF(
[Expected Tasks] > 0,
CALCULATE(
COUNTX(
FILTER(
Tasks,
LEFT(Tasks[Expected Date],6) = LEFT(MAX('Calendar'[Data_id]),6) && /* Filter Tasks of this month
RELATED('Calendar'[Ano]) = MAX('Calendar'[Ano]) &&*/
Tasks[Conclusion Date] <= MAX('Calendar'[Data_id])
),
Tasks[Task]
),
CROSSFILTER(
Tasks[Expected Date],'Calendar'[Data_id],None
)
)+0,
BLANK()
)And I got this:
I try to upload in the question what I was expectin 4 times, but does not work, so I will try here to: