Solved: Urgent help calculating monthly measures or consum...

AOHORQUEZ · ‎01-06-2021

I have a table with more than a million records with all inventory trtansations.

but I need to heat up total consumption or my life separately.

Tabla data:

Code	Item ID	Measure Std	Time.Date.1	Time.Month.1	Time.Year	AvgNoTurnDays	BaseQty	CodComp
AJI02	85087	A	10/31/2019	10	2019	0	13034	AJI0285087
AJI02	85087	A	11/30/2019	11	2019	0	5354	AJI0285087
AJI02	85087	A	12/31/2019	12	2019	2	282	AJI0285087
AJI02	85087	A	10/31/2020	10	2020	0	5696	AJI0285087
AJI02	85087	A	11/30/2020	11	2020	0	8910	AJI0285087
AJI02	85087	A	12/31/2020	12	2020	1	9325	AJI0285087

What I want to achieve is something like this, either through some calculated table or with measurements.

CodComp	Total Consumption 2019	Total Consumption 2020	Consumption month -1	Consumption month -2	Consumption month -3
AJI0285087	18670	23931	9325	8910	5696

I appreciate your cooperation.

Thank you.

AOHORQUEZ · ‎01-06-2021

What I've managed to do my way is a table with AN SUMMARIZE like this:
But I don't know... if e smjor with measurements

Consumos = 
var xMONTH1 = IF(MONTH(NOW())-1<=0,12,MONTH(NOW())-1)
var xMONTH2 = IF(MONTH(NOW())-2<=0,11,MONTH(NOW())-2)
var xMONTH3 = IF(MONTH(NOW())-3<=0,10,MONTH(NOW())-3)
var xMONTH4 = IF(MONTH(NOW())-4<=0,9,MONTH(NOW())-4)
var xMONTH5 = IF(MONTH(NOW())-5<=0,8,MONTH(NOW())-5)
var xMONTH6 = IF(MONTH(NOW())-6<=0,7,MONTH(NOW())-6)
var xYEAR1 = IF(MONTH(NOW())-1<=0,YEAR(NOW())-1,YEAR(NOW()))
var xYEAR2 = IF(MONTH(NOW())-2<=0,YEAR(NOW())-1,YEAR(NOW()))
var xYEAR3 = IF(MONTH(NOW())-3<=0,YEAR(NOW())-1,YEAR(NOW()))
var xYEAR4 = IF(MONTH(NOW())-4<=0,YEAR(NOW())-1,YEAR(NOW()))
var xYEAR5 = IF(MONTH(NOW())-5<=0,YEAR(NOW())-1,YEAR(NOW()))
var xYEAR6 = IF(MONTH(NOW())-6<=0,YEAR(NOW())-1,YEAR(NOW()))
RETURN
SUMMARIZE(Inventory,
        Inventory[CodComp], 
        "Último Consumo Fecha", CALCULATE(MAX(Inventory[Time.Date.1]),FILTER(Inventory,Inventory[BaseQty] >0)),
        "Último Consumo Cant", CALCULATE(SUM(Inventory[BaseQty]),FILTER(Inventory,Inventory[Time.Date.1] = MAX(Inventory[Time.Date.1]))),
        "Consumo Total", CALCULATE(SUM(Inventory[BaseQty]),Inventory[Time.Year] = xYEAR1),
        "Consumo 1",     CALCULATE(SUM(Inventory[BaseQty]),Inventory[Time.Year] = xYEAR1,Inventory[Time.Month.1] = xMONTH1),
        "Consumo 2",     CALCULATE(SUM(Inventory[BaseQty]),Inventory[Time.Year] = xYEAR2,Inventory[Time.Month.1] = xMONTH2),
        "Consumo 3",     CALCULATE(SUM(Inventory[BaseQty]),Inventory[Time.Year] = xYEAR3,Inventory[Time.Month.1] = xMONTH3),
        "Consumo 4",     CALCULATE(SUM(Inventory[BaseQty]),Inventory[Time.Year] = xYEAR4,Inventory[Time.Month.1] = xMONTH4),
        "Consumo 5",     CALCULATE(SUM(Inventory[BaseQty]),Inventory[Time.Year] = xYEAR5,Inventory[Time.Month.1] = xMONTH5),
        "Consumo 6",     CALCULATE(SUM(Inventory[BaseQty]),Inventory[Time.Year] = xYEAR6,Inventory[Time.Month.1] = xMONTH6),
        "Consumo Total Últ.6 meses", ROUND(CALCULATE(SUM(Inventory[BaseQty]),DATESINPERIOD(CalendarCons[Date],MAX(Inventory[Time.Date.1]),-6,MONTH)),4),   
        "Consumo Promedio Últ.6 meses", ROUND(CALCULATE(SUM(Inventory[BaseQty]),DATESINPERIOD(CalendarCons[Date],MAX(Inventory[Time.Date.1]),-6,MONTH))/6,4),
        "Consumo Promedio año", ROUND(CALCULATE(SUM(Inventory[BaseQty]),DATESINPERIOD(CalendarCons[Date],MAX(Inventory[Time.Date.1]),-6,MONTH))/12,4)
        )

View solution in original post

amitchandak · ‎01-06-2021

@AOHORQUEZ , You can join you date with date of date table and use time intelligence

Example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
2 last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-24,MONTH)))

3 last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-36,MONTH)))

and so on

For year

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Refer why Time Intelligence can fail : https://youtu.be/OBf0rjpp5Hw

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

!! Power BI 101 Interview questions !! !! Master Microsoft Fabric- 36 Videos !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

ToddChitt · ‎01-06-2021

@AOHORQUEZ

Not sure what you are asking for.

Do you want a table with two columns of [Consumption for this year] and [Consumption for Last Year]?

This Year = CALCULATE ( SUM ( [Consumption] ), Year(Date) = Year(Today() ) )

Last Year = CALCULATE ( SUM ( [Consumption] ) Year(Date) = Year(Today()) - 1 )

Hope that helps to start.

Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!

AOHORQUEZ · ‎01-06-2021

Hello, really what I'm looking for to calculate is already a measure or a table that calculates example:
For each code: AJI0285087:

What was the Total Consumption year impaired
What was the Total Consumption of the current year
What was consumption month ago -1 last year
What was consumption month ago -2 last year
What was consumption month ago -3 last year
What was consumption month ago -4 last year
What was consumption month ago -5 last year
What was consumption month ago -6 last year

AOHORQUEZ · ‎01-06-2021

What I've managed to do my way is a table with AN SUMMARIZE like this:
But I don't know... if e smjor with measurements

Consumos = 
var xMONTH1 = IF(MONTH(NOW())-1<=0,12,MONTH(NOW())-1)
var xMONTH2 = IF(MONTH(NOW())-2<=0,11,MONTH(NOW())-2)
var xMONTH3 = IF(MONTH(NOW())-3<=0,10,MONTH(NOW())-3)
var xMONTH4 = IF(MONTH(NOW())-4<=0,9,MONTH(NOW())-4)
var xMONTH5 = IF(MONTH(NOW())-5<=0,8,MONTH(NOW())-5)
var xMONTH6 = IF(MONTH(NOW())-6<=0,7,MONTH(NOW())-6)
var xYEAR1 = IF(MONTH(NOW())-1<=0,YEAR(NOW())-1,YEAR(NOW()))
var xYEAR2 = IF(MONTH(NOW())-2<=0,YEAR(NOW())-1,YEAR(NOW()))
var xYEAR3 = IF(MONTH(NOW())-3<=0,YEAR(NOW())-1,YEAR(NOW()))
var xYEAR4 = IF(MONTH(NOW())-4<=0,YEAR(NOW())-1,YEAR(NOW()))
var xYEAR5 = IF(MONTH(NOW())-5<=0,YEAR(NOW())-1,YEAR(NOW()))
var xYEAR6 = IF(MONTH(NOW())-6<=0,YEAR(NOW())-1,YEAR(NOW()))
RETURN
SUMMARIZE(Inventory,
        Inventory[CodComp], 
        "Último Consumo Fecha", CALCULATE(MAX(Inventory[Time.Date.1]),FILTER(Inventory,Inventory[BaseQty] >0)),
        "Último Consumo Cant", CALCULATE(SUM(Inventory[BaseQty]),FILTER(Inventory,Inventory[Time.Date.1] = MAX(Inventory[Time.Date.1]))),
        "Consumo Total", CALCULATE(SUM(Inventory[BaseQty]),Inventory[Time.Year] = xYEAR1),
        "Consumo 1",     CALCULATE(SUM(Inventory[BaseQty]),Inventory[Time.Year] = xYEAR1,Inventory[Time.Month.1] = xMONTH1),
        "Consumo 2",     CALCULATE(SUM(Inventory[BaseQty]),Inventory[Time.Year] = xYEAR2,Inventory[Time.Month.1] = xMONTH2),
        "Consumo 3",     CALCULATE(SUM(Inventory[BaseQty]),Inventory[Time.Year] = xYEAR3,Inventory[Time.Month.1] = xMONTH3),
        "Consumo 4",     CALCULATE(SUM(Inventory[BaseQty]),Inventory[Time.Year] = xYEAR4,Inventory[Time.Month.1] = xMONTH4),
        "Consumo 5",     CALCULATE(SUM(Inventory[BaseQty]),Inventory[Time.Year] = xYEAR5,Inventory[Time.Month.1] = xMONTH5),
        "Consumo 6",     CALCULATE(SUM(Inventory[BaseQty]),Inventory[Time.Year] = xYEAR6,Inventory[Time.Month.1] = xMONTH6),
        "Consumo Total Últ.6 meses", ROUND(CALCULATE(SUM(Inventory[BaseQty]),DATESINPERIOD(CalendarCons[Date],MAX(Inventory[Time.Date.1]),-6,MONTH)),4),   
        "Consumo Promedio Últ.6 meses", ROUND(CALCULATE(SUM(Inventory[BaseQty]),DATESINPERIOD(CalendarCons[Date],MAX(Inventory[Time.Date.1]),-6,MONTH))/6,4),
        "Consumo Promedio año", ROUND(CALCULATE(SUM(Inventory[BaseQty]),DATESINPERIOD(CalendarCons[Date],MAX(Inventory[Time.Date.1]),-6,MONTH))/12,4)
        )

Urgent help calculating monthly measures or consumption table

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly