Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good morning, i need some help with this process. i have this information for some counters, create every month and the data in cummulative. i want to know how many items i have every month.
Contador Month Total
Pablo | 1/1/2021 | 100 |
Pablo | 2/1/2021 | 300 |
Pablo | 3/1/2021 | 350 |
Pablo | 4/1/2021 | 480 |
Pablo | 5/1/2021 | 620 |
Pablo | 6/1/2021 | 715 |
Pablo | 7/1/2021 | 920 |
Pablo | 8/1/2021 | 1021 |
Pablo | 9/1/2021 | 1130 |
Pablo | 10/1/2021 | 1312 |
Pablo | 11/1/2021 | 1420 |
Pablo | 12/1/2021 | 1550 |
Juan | 1/1/2021 | 40 |
Juan | 2/1/2021 | 70 |
Juan | 3/1/2021 | 95 |
Juan | 4/1/2021 | 130 |
Juan | 5/1/2021 | 153 |
Juan | 6/1/2021 | 180 |
Juan | 7/1/2021 | 195 |
Juan | 8/1/2021 | 240 |
Juan | 9/1/2021 | 258 |
Juan | 10/1/2021 | 274 |
Juan | 11/1/2021 | 298 |
Juan | 12/1/2021 | 321 |
i tried with meassures but i think i'm doing something Wrong.
Thanks in advance
Solved! Go to Solution.
Hi @MiguelEro ,
According to my understanding, you want to calculate the difference between current month and previous month to get the actual total for each month of each Contador ,right?
Please try:
Vs Last Month =
VAR _preMonth =
CALCULATE ( SUM ( 'Table'[Total] ), PREVIOUSMONTH ( 'Table'[Month] ) )
RETURN
IF ( _preMonth = BLANK (), BLANK (), SUM ( 'Table'[Total] ) - _preMonth )
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @MiguelEro ,
According to my understanding, you want to calculate the difference between current month and previous month to get the actual total for each month of each Contador ,right?
Please try:
Vs Last Month =
VAR _preMonth =
CALCULATE ( SUM ( 'Table'[Total] ), PREVIOUSMONTH ( 'Table'[Month] ) )
RETURN
IF ( _preMonth = BLANK (), BLANK (), SUM ( 'Table'[Total] ) - _preMonth )
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@MiguelEro if I understand correctly you need cumulative total. Try with that:
if you additionally also want to cumulate over year by year add Max('Calendar[Year]) into it:
thanks for your Quick response
Hi @MiguelEro ,
I'm not sure I completely understand the question. Can you provide an example of the desired output?
Jori
Thanks Jori, this is the spected output
ContadorMonthTotalVs Last Month
Pablo | 1/1/2021 | 100 | |
Pablo | 2/1/2021 | 300 | 200 |
Pablo | 3/1/2021 | 350 | 50 |
Pablo | 4/1/2021 | 480 | 130 |
Pablo | 5/1/2021 | 620 | 140 |
Pablo | 6/1/2021 | 715 | 95 |
Pablo | 7/1/2021 | 920 | 205 |
Pablo | 8/1/2021 | 1021 | 101 |
Pablo | 9/1/2021 | 1130 | 109 |
Pablo | 10/1/2021 | 1312 | 182 |
Pablo | 11/1/2021 | 1420 | 108 |
Pablo | 12/1/2021 | 1550 | 130 |
Juan | 1/1/2021 | 40 | |
Juan | 2/1/2021 | 70 | 30 |
Juan | 3/1/2021 | 95 | 25 |
Juan | 4/1/2021 | 130 | 35 |
Juan | 5/1/2021 | 153 | 23 |
Juan | 6/1/2021 | 180 | 27 |
Juan | 7/1/2021 | 195 | 15 |
Juan | 8/1/2021 | 240 | 45 |
Juan | 9/1/2021 | 258 | 18 |
Juan | 10/1/2021 | 274 | 16 |
Juan | 11/1/2021 | 298 | 24 |
Juan | 12/1/2021 | 321 | 23 |
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |