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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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 III
Resolver III

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_0-1654776204686.png

 

View solution in original post

Hi @Anonymous 

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 @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

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 @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

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.
Anonymous
Not applicable

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 III
Resolver III

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_0-1654776204686.png

 

Anonymous
Not applicable

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 @Anonymous 

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

Anonymous
Not applicable

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 III
Resolver III

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
Not applicable

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

 

Anonymous
Not applicable

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 @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
Not applicable

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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