Calculations based on other 2 datasets

[GokilaRaviraj]

Hi,

I have 2 input tables shown below

CFY refers to Current FinYear and NFY refers to Next Finyear

Total Income

Company	OctCFY	NovCfy	DecCFY	JanNfy	Febnfy	Marnfy	Aprnfy	maynfy	junnfy	Julnfy	Augnfy	Sepnfy	Octnfy	Novnfy	Decnfy
A	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
B	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
C	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
D	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
E	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
F	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10

Admin Cost

Company	OctCFY	NovCfy	DecCFY	JanNfy	Febnfy	Marnfy	Aprnfy	maynfy	junnfy	Julnfy	Augnfy	Sepnfy	Octnfy	Novnfy	Decnfy
A	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20
B	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20
C	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20
D	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20
E	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20
F	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20

I am showing months from current month + remaining months of the year + next year all the months. This will change dynamically. 

Output Table

VAT

Company	Jancfy	Febcfy	Marcfy	Aprcfy	maycfy	juncfy	Julcfy	Augcfy	Sepcfy	Octcfy	Novcfy	Deccfy	Jannfy	Febnfy	Marnfy	Aprnfy	maynfy	junnfy	Julnfy	Augnfy	Sepnfy	Octnfy	Novnfy	Decnfy
A	0	22.5	0	0	22.5	0	0	22.5	0	0	22.5	10	0	22.5	0	0	22.5	0	0	22.5	0	0	22.5	0
E	0	22.5	0	0	22.5	0	0	22.5	0	0	22.5	10	0	22.5	0	0	22.5	0	0	22.5	0	0	22.5	0
D	0	22.5	0	0	22.5	0	0	22.5	0	0	22.5	10	0	22.5	0	0	22.5	0	0	22.5	0	0	22.5	0

This calculations has to be done for every second month of the quarter. ie) Feb, May, Aug, Nov.   and It has to be done only when month is January. February value will be a manual input and the rest of all the months needs a calculation.

When month is jan, Report shows value from current year jan to next year december.

May = sum(total income of Jan+ feb+ mar)*25% + sum(admin cost of Jan + feb+ mar)*25%

Aug =sum(total income of Apr+ May+ Jun)*25% + sum(admin cost of Apr+ May+ Jun)*25%

Nov =sum(total income of Jul+ Aug+ Sep)*25% + sum(admin cost of Jul+ Aug+ Sep)*25%

Febnextyear= sum(total income of oct+ Nov+ Dec)*25% + sum(admin cost of oct+ Nov+ Dec)*25%

Maynextyear = sum(total income of Jannextyear+ febnextyear+ marnextyear)*25% + sum(admin cost of Jannextyear + febnextyear+ marnextyear)*25%

Augnextyear=sum(total income of Aprnextyear+ Maynextyear+ Junnextyear)*25% + sum(admin cost of Aprnextyear+ Maynextyear+ Junnextyear)*25%

Novnextyear =sum(total income of Julnextyear+ Augnextyear+ Sepnextyear)*25% + sum(admin cost of Julnextyear+ Augnextyear+ Sepnextyear)*25%

I am new to DAX. So i am not sure whether this can be done in DAX or M query. 

If any one has idea on this , your input would be a great help. 

[v-xinruzhu-msft]

Hi @GokilaRaviraj 

You can refer to the following sample.

1.First, you need to unpivot your tables to the following.

You can refer to the following queries.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyoBIRqxOt5ERtA52pbaALtQ10pbaBblQ1MBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, OctCFY = _t, NovCfy = _t, DecCFY = _t, JanNfy = _t, Febnfy = _t, Marnfy = _t, Aprnfy = _t, maynfy = _t, junnfy = _t, Julnfy = _t, Augnfy = _t, Sepnfy = _t, Octnfy = _t, Novnfy = _t, Decnfy = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"OctCFY", Int64.Type}, {"NovCfy", Int64.Type}, {"DecCFY", Int64.Type}, {"JanNfy", Int64.Type}, {"Febnfy", Int64.Type}, {"Marnfy", Int64.Type}, {"Aprnfy", Int64.Type}, {"maynfy", Int64.Type}, {"junnfy", Int64.Type}, {"Julnfy", Int64.Type}, {"Augnfy", Int64.Type}, {"Sepnfy", Int64.Type}, {"Octnfy", Int64.Type}, {"Novnfy", Int64.Type}, {"Decnfy", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company"}, "Attribute", "Value"),
    #"Uppercased Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", Text.Proper, type text}})
in
    #"Uppercased Text"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0oBIRqxOt5ERtA52pbaALtQ10pbaBblQ1MBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, OctCFY = _t, NovCfy = _t, DecCFY = _t, JanNfy = _t, Febnfy = _t, Marnfy = _t, Aprnfy = _t, maynfy = _t, junnfy = _t, Julnfy = _t, Augnfy = _t, Sepnfy = _t, Octnfy = _t, Novnfy = _t, Decnfy = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"OctCFY", Int64.Type}, {"NovCfy", Int64.Type}, {"DecCFY", Int64.Type}, {"JanNfy", Int64.Type}, {"Febnfy", Int64.Type}, {"Marnfy", Int64.Type}, {"Aprnfy", Int64.Type}, {"maynfy", Int64.Type}, {"junnfy", Int64.Type}, {"Julnfy", Int64.Type}, {"Augnfy", Int64.Type}, {"Sepnfy", Int64.Type}, {"Octnfy", Int64.Type}, {"Novnfy", Int64.Type}, {"Decnfy", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company"}, "Attribute", "Value"),
    #"Uppercased Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", Text.Proper, type text}})
in
    #"Uppercased Text"

2.Then create the following tables.

Type table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rc4xCoAwEETRu6T2EoJYBNTCMqSIYbUJYwhGyO0FYTPdY1iW75yxAfFsxg/OzHIol1CUYy5cm9JWkKnf1ku5S1Zu8VGu96ucJPYPAWAD2AA2cG1gA5nABrABbAAbwIaf/gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

Company table.

Company = SUMMARIZE('Admin Cost',[Company])

3.Create the following relationships among the tables.

4.Create the following measure.

MEASURE =
VAR a =
    GENERATESERIES ( 2, 23, 3 )
VAR b =
    SELECTEDVALUE ( 'Type'[Index] ) - 4
VAR c =
    SELECTEDVALUE ( 'Type'[Index] ) - 2
VAR d =
    CALCULATE (
        SUM ( 'Total Income'[Value] ),
        ALLSELECTED ( 'Type' ),
        'Type'[Index] <= c,
        'Type'[Index] >= b
    )
VAR e =
    CALCULATE (
        SUM ( 'Admin Cost'[Value] ),
        ALLSELECTED ( 'Type' ),
        'Type'[Index] <= c,
        'Type'[Index] >= b
    )
RETURN
    IF ( SELECTEDVALUE ( 'Type'[Index] ) IN a, d * 0.25 + e * 0.25, 0 )

5.Put the following fields to a matrix visual.

Output

Best Regards!

Yolo Zhu

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

[GokilaRaviraj]

Hi @v-xinruzhu-msft 

Thanks for your reply.  I tried this measure. 

I am wondering why the values are not coming for Febcfy,Maycfy,Augcfy,Novcfy even though you have given this code. 

GENERATESERIES ( 2, 23, 3 )

I also have a question on type table month data, Now this has months from Jan cfy to dec nfy.  But I want to show these months from jancfy to decnfy only when current month is Jan.

when Current month is Feb, It has to show months from Febcfy to decnfy and this calculations is not needed. It has to just carry out the calculated vlaues previous month. Calculation has to be done again , when current month becomes Jan the next year

Is this can be done in DAX ? 

[v-xinruzhu-msft]

Hi @GokilaRaviraj 

Thanks for your quicl reply, for the first question, because the data you provided is start from October, so it will return 0 for Febcfy,Maycfy,Augcfy,Novcfy 

For the second question,  I don't really know the results you want

Best Regards!

Yolo Zhu

[GokilaRaviraj]

Hi @v-xinruzhu-msft 

Now I changed my 2 input tables with months from JanCFY to DecNFY.

In the output table, Value is not showing up for FebCFY column alone. All other columns have values as per the measure.

Do you have any idea on this ?

[GokilaRaviraj]

Hi @v-xinruzhu-msft 

I have an another doubt here, This calculation has to be done only when current month is Jan. So i have put a condition like this in the measure, 

IF(FORMAT(TODAY(), "MMMM") ="January", Do the calculation. But in else part, I need to just carry out the same value which was calculated on January. It should not again calculate anything, since my input tables will not have jan value to calculate May value.

Is this can be done ?

[v-xinruzhu-msft]

Hi @GokilaRaviraj 

There is no value in February because there is no value in the previous quarter based on February. Do you mean that February returns the value calculated in January? Then there is another question. If the current month is January, the calculation you described above is performed, and then the value calculated in January is returned. Do you need to perform the calculation performed in the second month of each subsequent quarter mentioned above? I am a little confused.

Best Regards!

Yolo Zhu

[GokilaRaviraj]

Hi @v-xinruzhu-msft 

Sorry for the confusion.

1) This entire calculation has to be done only when Current month is jan. 

Otherwise from next month onwards, Calculation is not needed. Whatever values we have calculated on Jan month on this table for febcfy,Maycfy,Augcfy,Novcy,Febnfy,Maynfy,Augnfy and Novnfy for these columns should be retained. 

Table will start show months from FebCfy to Decnfy the next month as per my calendar table.

Since our input table will not have previous quarters, we can not do the calculations again on the upcoming months. 

2) I need this calculation as a calculated column since i need to use this table and values further to proceed with append query. Could you please help me with this?

[v-xinruzhu-msft]

Hi @GokilaRaviraj 

Thanks for your quick reply, for the first question, because the data you provided is start from October, so it will return 0 for Febcfy,Maycfy,Augcfy,Novcfy 

• 

For the second question,  what is the logic of  the calculated vlaues previous month,and do you use the today() to define the current month?

Best Regards!

Yolo Zhu