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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Fill a column based on last month date

Hello!

 

I need to create a calulated column "NEW COLUM", placing the last month values acording to the  "tipe" column, 

and olny the last month f.e I had 2700000 kg of CPRS in January, i need the same value for fabruary

 KG2.PNG

I will appreciate someone´s help

 

Thanks

 

1 ACCEPTED SOLUTION
LivioLanzo
Solution Sage
Solution Sage

Try like this:

 

= CALCULATE(

        SUM( 'Table'[kGS] ),

        ALLEXCEPT( 'Table', 'Table'[Type] ),

        'Table'[Month] = EARLIER( 'Table'[Month] ) - 1

)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

5 REPLIES 5
edhans
Super User
Super User

You'll need a date table to do this properly. Then the following measure will give you the previous month sales. The filter context of the table/matrix will keep it to just items of the same Type.

 

=CALCULATE(
	[Total Sales],
	PREVIOUSMONTH('Calendar'[Date])
)


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
LivioLanzo
Solution Sage
Solution Sage

Try like this:

 

= CALCULATE(

        SUM( 'Table'[kGS] ),

        ALLEXCEPT( 'Table', 'Table'[Type] ),

        'Table'[Month] = EARLIER( 'Table'[Month] ) - 1

)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Can someone explain how the post marked as a solution works? My understanding of EARLIER is it has nothing to do with dates, but with contexts within a measure.

When I tried the measure marked as a solution, I get the following error, which is similar to what I expected.

Calculation error in measure 'SalesData'[measure 1]: EARLIER/EARLIEST refers to an earlier row context which doesn't exist.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans

 

you're probably using it in a measure which has no row context instead of a calculated column as the OP requested:

 

"

Hello!

 

I need to create a calulated column "NEW COLUM", placing the last month values acording to the  "tipe" column, 

and olny the last month f.e I had 2700000 kg of CPRS in January, i need the same value for fabruary"

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Ahhh... thanks. I never create calculated columns unless I need it in a measure, and even then, I tend to push it back to Power Query, so my bias there missed that part of the question. 🙂



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.