compare values from a matrix

[magcl]

Hello everyone,

My question is rather basic for those who master Power BI (this is not my case yet 😞) :

I have a simplified dataset with a number of an agent and a date on wich a certain task was performed :

agent	date	num_taches
Ag1	05/01/2023	1
Ag1	17/01/2023	1
Ag1	07/02/2023	1
Ag2	03/01/2023	1
Ag2	19/01/2023	1
Ag2	19/02/2023	1
Ag3	19/01/2023	1
Ag1	05/03/2023	1
Ag2	19/03/2023	1
Ag3	08/03/2023	1

I also have a table of dates to facilitate the management of dates:

date	annee	mois	jour	nom_jour_semaine	nom_mois	no_sem	nom_no_semaine	no_trimestre	nom_no_trimestre	weekend
01/01/2023 00:00	2023	1	1	Dimanche	Janvier	1	S1	1	T1	1
02/01/2023 00:00	2023	1	2	Lundi	Janvier	1	S1	1	T1	0
03/01/2023 00:00	2023	1	3	Mardi	Janvier	1	S1	1	T1	0
04/01/2023 00:00	2023	1	4	Mercredi	Janvier	1	S1	1	T1	0
05/01/2023 00:00	2023	1	5	Jeudi	Janvier	1	S1	1	T1	0
06/01/2023 00:00	2023	1	6	Vendredi	Janvier	1	S1	1	T1	0
07/01/2023 00:00	2023	1	7	Samedi	Janvier	1	S1	1	T1	1
... etc										1

-There is a relationship between two tables with the 'date' field-

I need to check if each agent performs a task at least once a month. This means that in March an agent must reach the accumulation of 3 tasks not to be 'late'.

So far I managed to create a sum of the tasks :

sum_tache = sum('my_table'[num_taches]

... and then make the cumulation :

cum_tache = CALCULATE('my_table'[sum_tache], DATESYTD(('my_calendar'[date])))

...which gives the following result on the visual matrix:

Q1: I would like the date to be displayed in month format, but when I choose the 'month' field from my 'date' table, the cumulation of values does not work.

Last problem: I need my matrix to display in a binary way if the cumulation for a given month is equal to the month, e.g. for ag_3
For January: he performed 1 tasks so we display "1"
For February: its cumulation is equal always at 1 so we display "0"
For March: it has performed 1 task - its cumulation is at 2 - so we display "0" (it should be at 3)

Q2 : So my question is - is it possible to compare the cumulative values with an int value that represents the month concerned?

Thank you in advance for your help.

Best regards,

Mag Cle

[Anonymous]

HI @magcl,

Time intelligence function does not support to handle aggregate values, perhaps you can try to use date function extract the current date and use this as conditions to calculated with your expressions:

Time Intelligence "The Hard Way" (TITHW)  

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@magcl , if you select month year, then it should work

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

Or try window function

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s