Forum Discussion
Helper III4 quarter collection
Merhaba
Her şey normale dönene kadar 4 çeyrekte geriye doğru kar toplamaya çalışıyorum
4 çeyreğin eksik verilerinin toplamını almak istemiyorum. Toplamların 4 çeyreğe ait veri içerip içermediğini nasıl kontrol edebilirim?
Birikimli Kar =
VAR Donem1 = MAX('Dönem'[Dönem])
VAR Bitiş2 =
HESAPLAMAK(
MAX('Dönem'[Dönem]),
FİLTRE(
TÜMÜ('Dönem'),
'Dönem'[Dönem] < Donem1
))
VAR Bitiş3 =
HESAPLAMAK(
MAX('Dönem'[Dönem]),
FİLTRE(
TÜMÜ('Dönem'),
'Dönem'[Dönem] < Donem2
))
VAR Bitiş4 =
HESAPLAMAK(
MAX('Dönem'[Dönem]),
FİLTRE(
TÜMÜ('Dönem'),
'Dönem'[Dönem] < Donem3
))
VAR TamamKar1 =
HESAPLA([Net Kar],
'Dönem'[Dönem] = Donem1
)
VAR TamamKar2 =
HESAPLA([Net Kar],
'Dönem'[Dönem] = Donem2
)
VAR BitişKar3 =
HESAPLA([Net Kar],
'Dönem'[Dönem] = Donem3
)
VAR TamamKar4 =
HESAPLA([Net Kar],
'Dönem'[Dönem] = Dönem4
)
GERİ DÖNMEK
DonemKar1 +DonemKar2 +DonemKar3 + DonemKar4
| Period | Value | 4 Quarter Total | |
| 2024/06 | 175.809.561,00 | 3.101.989.727,00 | Evet |
| 2024/03 | -11.110.597,00 | 3.398.882.907,00 | Evet |
| 2023/12 | 1.776.945.363,00 | 4.446.245.751,00 | Evet |
| 2023/09 | 1.160.345.400,00 | 2.669.300.388,00 | Hayır |
| 2023/06 | 472.702.741,00 | 1.508.954.988,00 | Hayır |
| 2023/03 | 1.036.252.247,00 | 1.036.252.247,00 | Hayır |
No need to do this in DAX?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY7LDYQwFAN7yTk4fn9SC6L/NhYJIm0OPs3Y8nU1pfpgDkrrTSpwciJSOtnuvri9/BCBCBGz/rgN0a+PqsT0gKVtBucyJAl7DCd34/vgpSg+cdm5rQVaQkOh/r64fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type date}, {"Value", Currency.Type}},"tr"), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Period", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "4 Quarter Total", (k)=> List.Sum(Table.SelectRows(#"Added Index",each [Index]<=k[Index] and [Index]>=k[Index]-3)[Value]),Currency.Type), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Complete", (k)=> if Table.RowCount(Table.SelectRows(#"Added Index",each [Index]<=k[Index] and [Index]>=k[Index]-3))=4 then "yes" else "no", type text), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index"}), #"Sorted Rows1" = Table.Sort(#"Removed Columns",{{"Period", Order.Descending}}) in #"Sorted Rows1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
But if you want - here is the DAX version
3 Replies
- lbendlin
Super User
No need to do this in DAX?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY7LDYQwFAN7yTk4fn9SC6L/NhYJIm0OPs3Y8nU1pfpgDkrrTSpwciJSOtnuvri9/BCBCBGz/rgN0a+PqsT0gKVtBucyJAl7DCd34/vgpSg+cdm5rQVaQkOh/r64fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type date}, {"Value", Currency.Type}},"tr"), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Period", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "4 Quarter Total", (k)=> List.Sum(Table.SelectRows(#"Added Index",each [Index]<=k[Index] and [Index]>=k[Index]-3)[Value]),Currency.Type), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Complete", (k)=> if Table.RowCount(Table.SelectRows(#"Added Index",each [Index]<=k[Index] and [Index]>=k[Index]-3))=4 then "yes" else "no", type text), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index"}), #"Sorted Rows1" = Table.Sort(#"Removed Columns",{{"Period", Order.Descending}}) in #"Sorted Rows1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
But if you want - here is the DAX version
MekistHello
The original file contains the company name and other data, there is more data, I have sent short data as an example.
The measure you shared created the 2024/09 period. That period does not exist for this company. How can we overcome this?
- lbendlin
I see that period.
You would need to adjust the measure to use the fact tables, not the dimension tables. Your Hisse column doesn't drive any dates.