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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Do5779
Helper II
Helper II

Apply costs per 12 months and get the results of a periode and impact of the periode before

Hello experts,

 

I have the following problem and stuck here:

 

I created a visual, which shows the comulative costs per 12 month, which only apperars one time in my table/dataset. For example:

 

DateCustomerCosts
01.01.202211
09.02.202228
07.06.2022310


I got a stacked bar chart like in the following example, but without expanding the dataset, which works fine. 

Do5779_1-1671460355632.png

 

Now I am searching for a flexible solution

  • to get the result of the costs in general in the shown period
  • and the costs from the periode before.

     So I can say, okay In 2023 there are total costs  58 and costs from the year before of 58 ( no additional costs in 2023).  I have a Slicer to select year, month, quarter etc. and need a flexible way to select maybe quarter and get the results for this and the quarter before. And I have no idea how I can get a suitable solution. 
    Has anyone an idea?


    Thanks a lot!
5 REPLIES 5
Do5779
Helper II
Helper II

I feel sorry for, but in this forum I have no possibility to load up a file and don't have access and the possibility to use Ondrive or something else.

Let me explain it in another way:
So here is the complete overview of the whole periode (01.01 2020 to 01.01.2024), where you find cumulative costs per month. From Januar 2022 to May 2023. The costs are always divided into 12 months. Interesting is only the month, not the exact date, when the cost occours.

Do5779_0-1671604242690.png

 

So if you put the slicer to December 2021 to March 2023 I get these diagramm:

Do5779_1-1671605537903.png
The total costs are the sum over the shown periode, in this case 34. The costs, whiche comes from a periode before are 0. So in this case all the costs of every customer occour in the shown periode.

Part Costs from previous periodeTotal Costs  Part Costs from actual periode
0 34 34


Next example:
In this periode ( February 2022 to March 2022) there are total costs 28. 

Do5779_2-1671606212527.png
The calculated part cost of the actual periode is 16. Why?In the part cost there should be only the costs, which occours in the periode the first time, which are in this case the costs of Customer C2 ( first time in February 2022). All the other costs (5 (C4) *2 +1(C1) *2 = 12) are impact of a timestamp before.

Part Costs from actual periode Total Costs part costs from previous timestamp/periode
16 ( 2*C2) 28 12 (C1+C4)*2

 


Example 3:
In this case are total costs 130 and 0 costs, which occours the first time. The whole costs are the impact of a periode before.
130 = 3*1 (C1) +4*8(C2)+10*8(C3)+3*5 (C4)

Do5779_4-1671607245027.png

 

 

Part Costs from actual periode Total Costs part costs from previous timestamp/periode
0 130 
130




I hope it is clearer?

Table withe dates

Do5779_6-1671607395729.png

 

 

 



Do5779
Helper II
Helper II

@Ashish_Mathur @v-yangliu-msft thank you for your tips. I would like to upload my data, but it doesnt work, so in this case it might be clearer what is it about,.
Only this message occours:

The file type (.pbix) is not supported.


Back to the topic:
So I am searching for a Measure to sum up alle the data, wich is displayed in the visual
and also get the impact of costs, which originally occours in the previous timespan, but also displayed in the choosen one.

Do5779_2-1671524557111.png

 

Do5779_3-1671524569226.png

The visual is based on the following Measure:

 

 

Monthly_Costs = 


calculate([CostsTotal]/12
    
    ,DATESINPERIOD(caledar[Date], max(caledar[Date]),-1,year)


)

 

 

 

 

 

 

CostsTotal = sum('Tabelle (2)'[Annual Costs])

 

 


In reality there is no Slicer, but you can chose Year, Quarter , Month.

Source-Data for the Visual/Measure only:

Do5779_4-1671524865535.png

 





Thanls a lot for your help!



I still do not know what you want.  Show the expected result in a simple Table format.  Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

Explore the SAMEPERIODLASTYEAR() function. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yangliu-msft
Community Support
Community Support

Hi  @Do5779 ,

I created some data:

vyangliumsft_0-1671502497662.png

Here are the steps you can follow:

1. Create measure.

now =
var _min=MINX(ALLSELECTED('Table'),[Date])
var _max=MAXX(ALLSELECTED('Table'),[Date])
var _select=SELECTEDVALUE('Slicer_Table'[Slicer])
return
SWITCH(
    TRUE(),
    MAX('Slicer_Table'[Slicer])="year",
    SUMX(FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])&&'Table'[Year]=MAX('Table'[Year])&&'Table'[Group]=MAX('Table'[Group])),[Value]),
    MAX('Slicer_Table'[Slicer])="month",
    SUMX(FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])&&'Table'[Year]=MAX('Table'[Year])&&'Table'[Group]=MAX('Table'[Group])&&'Table'[Month]=MAX('Table'[Month])),[Value]),
    MAX('Slicer_Table'[Slicer])="quarter",
    SUMX(FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])&&'Table'[Year]=MAX('Table'[Year])&&'Table'[Group]=MAX('Table'[Group])&&'Table'[Qu]=MAX('Table'[Qu])),[Value]))
last =
var _min=MINX(ALLSELECTED('Table'),[Date])
var _max=MAXX(ALLSELECTED('Table'),[Date])
var _select=SELECTEDVALUE('Slicer_Table'[Slicer])
return
SWITCH(
    TRUE(),
    MAX('Slicer_Table'[Slicer])="year",
    SUMX(FILTER(ALL('Table'),'Table'[Date]<=DATE(YEAR(MAX('Table'[Date]))-1,MONTH(MAX('Table'[Date])),DAY(MAX('Table'[Date])))&&'Table'[Year]=MAX('Table'[Year])-1&&'Table'[Group]=MAX('Table'[Group])),[Value]),
    MAX('Slicer_Table'[Slicer])="month",
    SUMX(FILTER(ALL('Table'),'Table'[Date]<=DATE(YEAR(MAX('Table'[Date])),MONTH(MAX('Table'[Date]))-1,DAY(MAX('Table'[Date])))&&'Table'[Group]=MAX('Table'[Group])&&'Table'[Year]=MAX('Table'[Year])&&'Table'[Month]=MAX('Table'[Month])-1),[Value]),
    MAX('Slicer_Table'[Slicer])="quarter",
    SUMX(FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])&&'Table'[Year]=MAX('Table'[Year])&&'Table'[Group]=MAX('Table'[Group])&&'Table'[Qu]=MAX('Table'[Qu])-1),[Value]))

2. Enter data – Create table.

vyangliumsft_1-1671502497664.png

3. Result:

vyangliumsft_2-1671502497666.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helpful resources

Announcements
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!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.