cancel
Showing results 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

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 =
Datas_IR_Long_Short,
"@Result_Apur_Long",
VAR Data_Ref = [Data]
RETURN
CALCULATE(
cIRLong[Data] = Data_Ref
),
"@Result_Apur_Short",
VAR Data_Ref = [Data]
RETURN
CALCULATE(
cIRShort[Data] = Data_Ref
)
)
RETURN
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:

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
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.

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:

I attach the pbix file for this exercise below:

Only on the 1st row of month.pbix

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.

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:

I attach the pbix file for this exercise below:

Only on the 1st row of month.pbix