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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Aroth
Advocate II
Advocate II

Show Changes between two given periods

Hi, 

I'm working on a report that shows every months the lists of projects we have in our CRM.

I would like to show the changes in projects from a period to another but I have some issues.

What I have currently :

Let's say that in october we had 3 Projects as below (With Px a given project):

October amount
P112
P25
P35
Total22

 

And in december we have that :

December
P26
P34
P410
Total20

 
So Project 1 has been lost and Project 4 has been created.

I've create a simple formula that calculate the difference between October and December and when I display in a table with the poject in rows that's what I see:

Changes Amount
Total-2
P21
P3-1

 

So the Total is correct, I went from 22 to 20 so it reduce from 2. But in my table I see only the two projects october and december have in commun. And what I would like to see is that :

Changes amount
Total-2
P1-12
P21
P3-1
P410

 

My formula is simply: 

Net Changes = CALCULATE( Net sales, Month = December) - CALCULATE(Net sales, Month = October).


Thank you for reading my text, if you have an idea on how to solve my issue it would be great.

Thanks,
AROTH

1 ACCEPTED SOLUTION

Hi @Aroth,

 




Measure = CALCULATE([net]),FILTER(ALL(MONTHS),MONTHS[index] = MAX(MONTHS[index]))-CALCULATE([net]),FILTER(ALL(MONTHS), MONTHS[index] = MIN(MONTHS[index] )

 

Based on above measure, I could get the desired output.

 

What we need is add a MonthNo column to original table (suppose it's 'Month-Project'), and insert an extra table (in my test, it's named as 'Month') listing unique month values.

1.PNG2.PNG

 

Create measure like:

Diff =
CALCULATE (
    ( [net] ),
    FILTER ( 'Month-Project', 'Month-Project'[MonthNo] = MAX ( 'Month'[MonthNo] ) )
)
    - CALCULATE (
        ( [net] ),
        FILTER ( 'Month-Project', 'Month-Project'[MonthNo] = MIN ( 'Month'[MonthNo] ) )
    )

Add field [Month] from 'Month' table. Add [Project] and [Diff] from 'Month-Project' table.

3.PNG

 

Best regards,

Yuliana Gu

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

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

I think that if you use EXCEPT along with a UNION and a NATURALINNERJOIN that you could essentially return a table with all of your exceptions. So the NATURALINNERJOIN would find the matching rows between the tables. UNION would UNION your tables together and so if you take the EXCEPT of the UNION and NATURALINNERJOIN you would have the differences.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Can you kindly elaborate more with the simple data set provided by requester. Also can you help how to solve it with drill through

@Greg_Deckler thank you for answer, I'm not sure how I can use that as everything is on the same data base (table):

MonthProjectnet
OctoberP112
OctoberP25
OctoberP35
DecemberP26
DecemberP34
DecemberP410

 

That's why I use CALCULATE to filter on one month and substract by another CALCULATE filtered on an other month.
And my "net" field is a measure...

Wow, I way over complicated that. Just create a measure:

 

Measure = CALCULATE(SUM(Projects[net]),Projects[Month]="December")-CALCULATE(SUM(Projects[net]),Projects[Month]="October")

Then put your Project column and this measure into a Table visualization. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

That's what I did, the only thigs Iv'e added is :

Measure = CALCULATE([net]),FILTER(ALL(MONTHS),MONTHS[index] = MAX(MONTHS[index]))
-CALCULATE([net]),FILTER(ALL(MONTHS), MONTHS[index] = MIN(MONTHS[index] )

 So it enable to compare two selected periods ...

Hi @Aroth,

 




Measure = CALCULATE([net]),FILTER(ALL(MONTHS),MONTHS[index] = MAX(MONTHS[index]))-CALCULATE([net]),FILTER(ALL(MONTHS), MONTHS[index] = MIN(MONTHS[index] )

 

Based on above measure, I could get the desired output.

 

What we need is add a MonthNo column to original table (suppose it's 'Month-Project'), and insert an extra table (in my test, it's named as 'Month') listing unique month values.

1.PNG2.PNG

 

Create measure like:

Diff =
CALCULATE (
    ( [net] ),
    FILTER ( 'Month-Project', 'Month-Project'[MonthNo] = MAX ( 'Month'[MonthNo] ) )
)
    - CALCULATE (
        ( [net] ),
        FILTER ( 'Month-Project', 'Month-Project'[MonthNo] = MIN ( 'Month'[MonthNo] ) )
    )

Add field [Month] from 'Month' table. Add [Project] and [Diff] from 'Month-Project' table.

3.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi thanks for the support, I have the same scenario, when I use the fax in drill through option. It doest show correct caluculation. Can you kindly help what could be the possible issue and why Im not able get the values populated correctly when I used the fax as a column in tablix which is created for drill trhrrough. I have proper relationships with in table. Happy to discuss

Thanks !!
I finaly understood my issue. The relation between the two tables had the cross filter selection in both ways!! 
I changed it to "single" and now it works 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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