Comparing values of months

[Mekaur]

Hello,

I have date column and RAGID measure.I need to compare values of last n month.

If user select 3 then last 3 months comparison should show,4 then last 4 months and so on.

Example:

Product           Date:                RAGID

 Pen                 Feb 2020             1

Pen                  March 2020         0

Pen                  April 2020            2

Pencil               Feb 2020             0

Pencil               March 2020         2

Pencil               April 2020            0

   0-Red,1-Amber,2-Green

I need to compare RAGID for three months  such as feb with march and march with April. Moreover, I need to see whether its performance is deteriorating (1-0) and then improving(0-2) and (0-0) stable.

How can I compare the values of months having in one column?

Please let me know if you need more information.

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[v-xicai]

Hi @Mekaur ,

You may create columns like DAX below.

Diff=

Var _LastDate=  CALCULATE(MAX(Table1[Date]),FILTER(ALLSELECTED(Table1),Table1[Product]=EARLIER(Table1[Product]) && Table1[Date]<EARLIER(Table1[Date])))

Var _LastRAGID= CALCULATE(SUM(Table1[RAGID]),FILTER(ALLSELECTED(Table1),Table1[Product]=EARLIER(Table1[Product]) && Table1[Date]= _LastDate))

Return

IF( _LastRAGID= BLANK(), 0, [RAGID] -_LastRAGID )



Performance = SWITCH(TRUE(), [Diff]=0,"stable", [Diff]>0,"improving",[Diff]<0, "deteriorating")

Best Regards,

Amy 

Community Support Team _ Amy

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

[Mekaur]

Hi @v-xicai 

Thank you for your reply.

May I know why are you taking SUM(date) in 

_LastRAGID

 I can't understand that.

[v-xicai]

Hi @Mekaur ,

Sorry for my mistake. I have corrected the formula above, and you may try it again.

Best Regards,

Amy 

[Mekaur]

Hello @v-xicai 

I am not getting desired results.

Can you please have a look at the question once again.I have made some changes.

I think you are pretty close to the solution.

🙂

Regards,

Mekaur

[v-xicai]

Hi @Mekaur ,

Could you please share your current result using the formula above and desired output screenshots for further analysis?

Best Regards,

Amy 

[amitchandak]

@Mekaur , You can use the time intelligence date table. If you have a date, join that with date table else try to create one like this

Date = "01 " & [Month Year] //Chnage data tye to date

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)))
MOM = [MTD Sales] -[last MTD Sales]

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[ryan_mayu]

@Mekaur 

Please check if below is the solution that you want.

compare with LM and NM = 
VAR LM = EDATE('Sheet1'[Date],-1)
VAR NM = EDATE('Sheet1'[Date],1)
VAR LM_RAGID = SUMX(FILTER('Sheet1',Sheet1[Product]=Sheet1[Product]&&Sheet1[Date]=LM),Sheet1[RAGID])
VAR NM_RAGID = IF(SUMX(FILTER('Sheet1',Sheet1[Product]=Sheet1[Product]&&Sheet1[Date]=LM),Sheet1[RAGID])=0,0,SUMX(FILTER('Sheet1',Sheet1[Product]=Sheet1[Product]&&Sheet1[Date]=NM),Sheet1[RAGID]))
VAR LM_RAGIDVSRAGID = if(ISBLANK(LM_RAGID),"-"&Sheet1[RAGID],LM_RAGID&"-"&Sheet1[RAGID])
VAR RAGIDVSNMRAGID = if(ISBLANK(NM_RAGID),Sheet1[RAGID]&"-",Sheet1[RAGID]&"-"&NM_RAGID)
RETURN LM_RAGIDVSRAGID&","&RAGIDVSNMRAGID

Thanks and BR

Ryan