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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
leolapa_br
Resolver I
Resolver I

Return sum of monthly values only on the first row of each month

I have the following code...

 

Tbl = 
VAR Datas_IR_Long = SUMMARIZECOLUMNS( cIRLong[Data] )
VAR Datas_IR_Short = SUMMARIZECOLUMNS( cIRShort[Data] )
VAR Datas_IR_Long_Short = 
	DISTINCT( 
		UNION( 
			Datas_IR_Long, 
			Datas_IR_Short 
		) 
	)
VAR Datas_IR_Long_Short_Result = 
	ADDCOLUMNS( 
		Datas_IR_Long_Short, 
		"@Result_Apur_Long", 
			VAR Data_Ref = [Data]
			RETURN
				CALCULATE( 
					SUM( cIRLong[Resultado apurado no mês] ), 
					cIRLong[Data] = Data_Ref 
				), 
		"@Result_Apur_Short", 
    		VAR Data_Ref = [Data]
			RETURN
				CALCULATE( 
					SUM( cIRShort[Resultado apurado no mês] ), 
					cIRShort[Data] = Data_Ref 
				)  
	)
RETURN
	ADDCOLUMNS( 
		Datas_IR_Long_Short_Result, 
		"@Result_Cons", 
		VAR Data_Ref = [Data]
		RETURN
			SUMX( 
				FILTER( 
					Datas_IR_Long_Short_Result, 
					FORMAT( [Data], "YYYYMM" ) = FORMAT( Data_Ref, "YYYYMM" ) 
				), 
				[@Result_Apur_Long] + [@Result_Apur_Short] 
			) 
	)

 

... that produces the following table:

Tbl.png

 

However, I can only male my goal column "@Result_Cons" repeat the monthly sum of "@Result_Apur_Long" + "@Result_Apur_Short" for every row under the same respective month.

 

I need something like the column on the far right (which is just an edited image for illustration purposes), showing each month's sum only under the first row of every month, returning the remaining rows as blanks.

 

I tried to solve it by inserting an IF statement with the following piece of code, but I get an error saying that the function LASTDATE cannot be used under ADDCOLUMNS or SUMMARIZE functions.

 

...
	VAR Datas = 
		SELECTCOLUMNS( 
			Datas_IR_Long_Short_Result, 
			"@Data", [Data] 
		)
	IF( 
		MONTH( Datas ) <> CALCULATE( 
			MONTH( LASTDATE( Datas ) ), 
			FILTER( 
				Datas_IR_Long_Short_Result, 
				[Data] < Data_Ref 
			) 
		), ...

 

How can I get that done?

1 ACCEPTED SOLUTION
DataNinja777
Super User
Super User

Hi leolapa_br,

Your visualization screen print seem to indicate that you could also produce what you required in Power BI, (as it doesn't look like an Excel screen print).  I also produced the result you required in Power BI.  

Sakiko_0-1693963902815.png

Steps taken are 

(1. Manually recreating the table in your screen print)

2. Created a YearMonth column in the table 'Tbl'.

3. Writing the following calculated column:

Sakiko_1-1693964043186.png

I attach the pbix file for this exercise below:

Only on the 1st row of month.pbix

 

View solution in original post

1 REPLY 1
DataNinja777
Super User
Super User

Hi leolapa_br,

Your visualization screen print seem to indicate that you could also produce what you required in Power BI, (as it doesn't look like an Excel screen print).  I also produced the result you required in Power BI.  

Sakiko_0-1693963902815.png

Steps taken are 

(1. Manually recreating the table in your screen print)

2. Created a YearMonth column in the table 'Tbl'.

3. Writing the following calculated column:

Sakiko_1-1693964043186.png

I attach the pbix file for this exercise below:

Only on the 1st row of month.pbix

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.