cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ParsiNitin
Helper I
Helper I

Change in Month to Month

Hello,

I have a Problem with my Power Query. I have a data where One column is having values and other having repetative months and other with repetative years and other with repetative sheet name. So, i want to get the monthly difference when i get the new value. Here is the one where iam attaching the picture. Can anyone please help me in this? 

In one word i want to get the month to month difference for each month and each year separately for each Lane.

ParsiNitin_0-1654605458766.png

So for the January 2022 (xxxx)if the value if 3647 and Feb 2022 (xxxx) if the value is 3947 in the visulaisation graph i should see the difference between these 2. Jan 22(xxxx) and Feb 22 (xxxx) might not be in the consecutive order in the query so i was confusing which formula i need to write.

 

3 ACCEPTED SOLUTIONS
Saap
Resolver II
Resolver II

Hi @ParsiNitin 

Check if your column's data types are correct.
Check if your measures have correct spelling.
Try to put your measures into matrix in correct way. I tried to do this and managed to create something like this:

Saap_0-1654776204686.png

 

View solution in original post

Hi @ParsiNitin 

I've uploaded the test pbix file: https://filetransfer.io/data-package/ltRlwarj#link
You can check how I did it.

View solution in original post

v-jayw-msft
Community Support
Community Support

Hi @ParsiNitin ,

 

Please refer this formula:

Measure =
VAR pre_month =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Lane], 'Table'[Attribute] ),
            'Table'[Index]
                = SELECTEDVALUE ( 'Table'[Index] ) - 1
        )
    )
RETURN
    SUM ( 'Table'[Value] ) - pre_month

vjaywmsft_0-1654845580735.png

Pbix as attachement.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

11 REPLIES 11
v-jayw-msft
Community Support
Community Support

Hi @ParsiNitin ,

 

Please refer this formula:

Measure =
VAR pre_month =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Lane], 'Table'[Attribute] ),
            'Table'[Index]
                = SELECTEDVALUE ( 'Table'[Index] ) - 1
        )
    )
RETURN
    SUM ( 'Table'[Value] ) - pre_month

vjaywmsft_0-1654845580735.png

Pbix as attachement.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Hi @Saap @v-jayw-msft 

thank you for the help. Both the solutions worked. I just added a slicer for selecting 2022(xx) , 2022 (yy)..... and so on till 2025 (zz). That really helped me a lot. Thank you once again both for helping me. 🙂

Saap
Resolver II
Resolver II

Hi @ParsiNitin 

Check if your column's data types are correct.
Check if your measures have correct spelling.
Try to put your measures into matrix in correct way. I tried to do this and managed to create something like this:

Saap_0-1654776204686.png

 

Hi @Saap 

I dont Know why, I have checked all the data types and Measures. Everything is perfect. Can you send me your calendar and sum picture as well because CALCULATE is refelction no value in my DAX I dont know why. 

ParsiNitin_0-1654783929645.png

I ended up like this

 

Hi @ParsiNitin 

I've uploaded the test pbix file: https://filetransfer.io/data-package/ltRlwarj#link
You can check how I did it.

Hi @Saap 

I understood now what was the mistake. The problem is the lane AAA and BBB in your file are only for 2022 but for my chart its 2022(xx), 2022(yy), 2022(zz). So, it is calculating all the values under the Lane AAA and showing as a one but i need to separate for 2022- 2022(xx), 2022(yy), 2022(zz) under lane AAA and then same for years 2023 under same lane AAA and 2024 for years 2024 again under same lane and then go to year 2022 with Lane BBB as 2022(xx), 2022(yy), 2022(zz) and same for years 2023, 2024 and so on how many years i add. So, it was calculating for 2022(xx+yy+zz) and showing the result combinely for Lane AAA but i need them separately. Your solution was absolutely correct but my question was not exact. i can understand that. A bit complicated to explain. if you find a solution for this can you please help me?

Saap
Resolver II
Resolver II

Hi @ParsiNitin 

You can try to do this like this:

1.  In your table create a date column. I see your index column is basicly your month number. Find the year using this calculated column:

Year = LEFT(YourTable[Attribute], 4)

Then create the date column:

Date = DATE(YourTable[Year], YourTable[Index], 1)

2. Create the time table. You can use the CALENDARAUTO function.

3. Create relationship between your time table and a date you just created.

4. Create a measure to sum your value:
Sum of value = SUM(YourTable[Value])

5. Create measure to count last month value:

Sum of value last month = CALCULATE([Sum of value], PREVIOUSMONTH('YourTimeTable'[Date])

6. Count the difference using measure:

Diff Value to Last Month = [Sum of value] - [Sum of value last month]

 

Hi @Saap @v-jayw-msft 

I have a small problem now. When I select 2different in slicer it is taking it as combined one. How can i get the 2 different graphs. Can any of you please help me? 3 and 4 should be represented in the form of 1 and 2 but it is showing them as combined one. 

ParsiNitin_0-1655121391439.png

 

Hi @Saap ,

 

I tried creating Auto calendar but the problem is there is no date/time column before in my table so it is showing an error. it cant take the new date as a calendar date. So can you please help me in this?

Hi @ParsiNitin 

Instead CALENDARAUTO you can use CALENDAR function where you specify the beginning and the end date of your table. Also you can check the Youtube on how to create time table.

See:
https://www.youtube.com/watch?v=BfFvNFTacNY
https://www.youtube.com/watch?v=BtYn1hfdSAM
https://www.youtube.com/watch?v=WybnTHDl-AM

Hi @Saap 

I tried doing with CALENDAR and connected with my timetable with the other one but I cannot get the result. 

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors