Change in Month to Month

[Anonymous]

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.

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.

[Saap · Accepted solution]

Hi @Anonymous 

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 · Accepted solution]

Hi @Anonymous 

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

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

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

Pbix as attachement.

Best Regards,

Jay

[Anonymous]

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 · Accepted solution]

Hi @Anonymous 

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:

[Anonymous]

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. 

I ended up like this

[Saap · Accepted solution]

Hi @Anonymous 

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

[Anonymous]

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]

Hi @Anonymous 

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]

[Anonymous]

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. 

[Anonymous]

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?

[Saap]

Hi @Anonymous 

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

[Anonymous]

Hi @Saap 

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