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

[Do5779]

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:

Date	Customer	Costs
01.01.2022	1	1
09.02.2022	2	8
07.06.2022	3	10

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

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!

[Do5779]

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.

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

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 periode	Total Costs	Part Costs from actual periode
0	34	34

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

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)

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]

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

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:

Thanls a lot for your help!

[Ashish_Mathur]

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.

[Ashish_Mathur]

Hi,

Explore the SAMEPERIODLASTYEAR() function. 

[v-yangliu-msft]

Hi  @Do5779 ,

I created some data:

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.

3. Result:

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