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

Get prior value from integer to calculate month over month

I'm trying to figure out way to calculate a month over month %  based on this data (ln_count).  However I don't want to use the actual month value because of the limitations with  filtering  and date hierarchy.

 

Instead i have an integer that uniquely represents the month & year (month_name_int)    

Anyone know the DAX code to do this ?

 

MAnderson1525_0-1634143295772.png

 

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

Hi  @Anonymous ,

Here are the steps you can follow:

1. Create calculated column.

Column =
var _group=CALCULATE(SUM('Table'[In_count]),FILTER(ALL('Table'),'Table'[month_name_int]=EARLIER('Table'[month_name_int])))
var _all=SUM('Table'[In_count])
return
DIVIDE(_group,_all)

2. Result:

vyangliumsft_0-1634610452903.png

Does this meet your expected result? If it does not, you can display the result in the form of an icon, and we will better solve the problem for you.

 

Best Regards,

Liu Yang

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

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

Here are the steps you can follow:

1. Create calculated column.

Column =
var _group=CALCULATE(SUM('Table'[In_count]),FILTER(ALL('Table'),'Table'[month_name_int]=EARLIER('Table'[month_name_int])))
var _all=SUM('Table'[In_count])
return
DIVIDE(_group,_all)

2. Result:

vyangliumsft_0-1634610452903.png

Does this meet your expected result? If it does not, you can display the result in the form of an icon, and we will better solve the problem for you.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

YukiK
Impactful Individual
Impactful Individual

If using month then you can use PREVIOUSMONTH(), but in your case, I suspect you can utilize EARLIER() function.

Anonymous
Not applicable

I think this solution will get you most of the way there: https://community.powerbi.com/t5/Desktop/Referencing-a-previous-Row/m-p/477748

 

Then all you need to do is calculate the percentage from that value you just looked up, something like:

Percentage of Previous Month = DIVIDE(In_count,ln_count_LM,0)

 

That zero at the end means that if last month column is blank or zero, it will just return 0 instead of an error.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.