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
Wilm117
Helper I
Helper I

Compare periods of data uploads (not necesarrily dates)

Hi everybody!

 

I am working on a report and I present the amount of money spend per period using this graph and the table underlyint this graph:

Wilm117_0-1623766449105.png

Wilm117_2-1623767488212.png

 

This is pretty straigthforward, but i am looking for a way to only visualize the amount of money spend per period instead of the cumulative of everything. As a quick solution i made seperate measures, where i manually subtracked Gebbedrag2 - Gebbedrag1 etc. The results are shown the following table.

 

Wilm117_1-1623766624571.png

However, it is difficult to make a nice visual with this and it will take manual work everytime i update this report. 

 

Does somebody know the right formulas to get to this point, without having to make new measures everytime i upload something? Underneath i posted a table with a simplified version of the data i use.

 

indexdate createdpostgeboekt bedrag
17-1-2021material2510841
17-1-2021staff6528437
17-1-2021overhead131079
17-1-2021food35334
229-1-2021material2543553
229-1-2021staff6528437
229-1-2021overhead131079
229-1-2021food35334
38-3-2021material2596334
38-3-2021staff6528437
38-3-2021overhead137673
38-3-2021food35334
47-4-2021material2655314
47-4-2021staff7738437
47-4-2021overhead143647
47-4-2021food35334

 

Thanks!

3 REPLIES 3
dedelman_clng
Community Champion
Community Champion

Hi @Wilm117 -

 

Unless I'm misunderstanding the question, you may just be over thinking it.  If you use Date Created as the column, Post as the rows, and SUM(geboekt bedrag) as the values it won't accumulate the sums over the entire table.

 

If you're meaning something else, please expand on what you are trying to accomplish.

 

Hope this helps

David

Hi Dedelman_clng,

 

Thanks for your reply. Maybe i was not clear enough in my explanation. I see your point, however, in this case i am only interested in the difference in the amount of money (Geboekt Bedrag) between every period (date created). Using "date created as column gives me a perfect overview on the total amount of "geboekt bedrag" eacht period, but how much more money is spend compared to the previous period. 

 

Now i make seperate calculations like:

Calculate(table1''[geboekte kosten]) , 'table1'[index] = "2") - Calculate(table1''[geboekte kosten]) , 'table1'[index] = "1")

i did this for each following period, but i would like to find a way how i can automatically calculate the added amount to the column "geboekte kosten" for each period.

 

Does this make it more clear? 

Hi @Wilm117 -

 

Here is a new column getting the value from the previous period, and then a measure to calculate (and display the difference.

 

PrevPd =
VAR __Idx = [index]
VAR __Post = [post]
RETURN
    LOOKUPVALUE (
        Geboekt[geboekt bedrag],
        Geboekt[index], __Idx - 1,
        Geboekt[post], __Post
    )

 

Delta =
IF (
    ISBLANK ( SUM ( Geboekt[PrevPd] ) ),
    BLANK (),
    SUM ( Geboekt[geboekt bedrag] ) - SUM ( Geboekt[PrevPd] )
)

 

2021-06-18 06_23_56-scratch4 - Power BI Desktop.png

 

Hope this helps

David

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.

Top Solution Authors