cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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.

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

Resolver III

Hi @Anonymous

You can check how I did it.

Community Support

Hi @Anonymous ,

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

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

Hi @Anonymous ,

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

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

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. 🙂

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:

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.

I ended up like this

Resolver III

Hi @Anonymous

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?

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

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

Resolver III

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:

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors