Calculate columns and rows with Filter year and month

[SvenJ]

Good evening,

ich have list, which looks like this and has the information of "Konto", "Incoming", "Outgoing", "Monat", "Year"

I have another table, which has a reference column "Konto von" - "Konto bis"

The result shoud be a matrix table, which sums the value by month and year, referenced by "Konto" in the specific row between "Konto von" - "Konto bis" I have different years, but the table is always the same. Can someone help me?

Can anyone help on this? 

[SvenJ]

@ValtteriN Thanks for your answer. As you can imagine, i would like to get this done by myself. It´s not because i don´t trust your solution, it´s more that i want to learn things around PowerBi

[Anonymous]

Hi @SvenJ ,

I see there may be different values in "Konto von" and "Konto bis" like in second row 8401 and 8402, is it a mistake?

If your logic is to refer "Konto" with "Konto von" and "Konto bis", you can try to achieve your goal by create measures.

Create a filter measure to filter your matrix visual. You can add this measure into visual level filter and set it to show items when value =1.

Measure = 
VAR _Konto_von = VALUES(Table2[Konto von])
VAR _Konto_bis = VALUES(Table2[Konto bis])
RETURN
IF(MAX(Table1[Konto]) IN _Konto_von ||MAX(Table1[Konto]) IN _Konto_bis,1,0)/* Or logic */
/* IF(MAX(Table1[Konto]) IN _Konto_von && MAX(Table1[Konto]) IN _Konto_bis,1,0) And logic */

M_Outgoing = 
CALCULATE(SUM(Table1[Outgoing]))

Result is as below.

Best Regards,
Rico Zhou

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

[SvenJ]

Hello @Anonymous , no it´s not a mistake. Some rows have values from f. ex. 8400 - 8499 an the result between this values should be the first row.

[Anonymous]

Hi @SvenJ ,

Do you mean that  8401 - 8402 is a range and the result is the first row 8401?

Try this filter measure:

Measure = 
VAR _Konto_von = VALUES(Table2[Konto von])
RETURN
IF(MAX(Table1[Konto]) IN _Konto_von,1,0)

If I have any misunderstanding , please share a screenshot with the result you want. This will make it easier for us to understand.

Best Regards,
Rico Zhou

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

[SvenJ]

I´m looking for the result which is between "Konto von" to "Konto bis"

f.ex if i have a value of 8969, i´m looking for the result "1040"

[Anonymous · Accepted solution]

Hi @SvenJ ,

I think you can create a calculated column to get [#BWA-Zeile] by filter of current "Table1"[Konto] in Range of

"Table2"[Konto von] and "Table2"[Konto bis].

#BWA-Zeile IN Range =
VAR _VALUE =
    CALCULATETABLE (
        VALUES ( Table2[#BWA-Zeile] ),
        FILTER (
            Table2,
            Table2[Konto bis] >= EARLIER ( Table1[Konto] )
                && Table2[Konto von] <= EARLIER ( Table1[Konto] )
        )
    )
RETURN
    CONCATENATEX ( _VALUE, _VALUE, "," )

Result is as below.

Best Regards,
Rico Zhou

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

[ValtteriN]

Hi,

The basic idea what you want to achieve in your data model is to connect General Ledger, Account Map and accounting dimensions together. For this kind of table structure you would want to do some changes in powerquery.

I notice that you are dealing with accounting data. I work for a Finnish company and our analytics teams specialises in building these kind of PowerBI solutions. We have what can be described as a "one click" solution which works on softwares such as Procountor, Business Central and Netvisor. 

Do check us out: https://get.bibook.com/