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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MiguelEro
Frequent Visitor

Month over month

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

Pablo1/1/2021100
Pablo2/1/2021300
Pablo3/1/2021350
Pablo4/1/2021480
Pablo5/1/2021620
Pablo6/1/2021715
Pablo7/1/2021920
Pablo8/1/20211021
Pablo9/1/20211130
Pablo10/1/20211312
Pablo11/1/20211420
Pablo12/1/20211550
Juan1/1/202140
Juan2/1/202170
Juan3/1/202195
Juan4/1/2021130
Juan5/1/2021153
Juan6/1/2021180
Juan7/1/2021195
Juan8/1/2021240
Juan9/1/2021258
Juan10/1/2021274
Juan11/1/2021298
Juan12/1/2021321

 

i tried with meassures but i think i'm doing something Wrong.

 

Thanks in advance

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

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 )

Eyelyn9_0-1634003423976.png

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.

View solution in original post

6 REPLIES 6
v-eqin-msft
Community Support
Community Support

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 )

Eyelyn9_0-1634003423976.png

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.

Thanks @v-eqin-msft , this is just what i was looking for.

Apreciate your help.

Applicable88
Impactful Individual
Impactful Individual

@MiguelEro  if I understand correctly you need cumulative total. Try with that:

Measure =
calculate(sum(Sales[Total]),
Filter(
ALL('Calendar'[Date]),
'Calendar'[Date]<= MAX('Calendar'[Date])
))

 

if you additionally also want to cumulate over year by year add Max('Calendar[Year]) into it:

 

Measure =
calculate(sum(Sales[Sales]),
Filter(
ALL('Calendar'[Date]),
'Calendar'[Date]<= MAX('Calendar'[Date])
&&
('Calendar[Year]=)Max('Calendar[Year])
))

 

 

thanks for your Quick response

jppv20
Solution Sage
Solution Sage

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

Pablo1/1/2021100 
Pablo2/1/2021300200
Pablo3/1/202135050
Pablo4/1/2021480130
Pablo5/1/2021620140
Pablo6/1/202171595
Pablo7/1/2021920205
Pablo8/1/20211021101
Pablo9/1/20211130109
Pablo10/1/20211312182
Pablo11/1/20211420108
Pablo12/1/20211550130
Juan1/1/202140 
Juan2/1/20217030
Juan3/1/20219525
Juan4/1/202113035
Juan5/1/202115323
Juan6/1/202118027
Juan7/1/202119515
Juan8/1/202124045
Juan9/1/202125818
Juan10/1/202127416
Juan11/1/202129824
Juan12/1/202132123

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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