Forum Discussion

GokilaRaviraj's avatar
GokilaRaviraj
Helper II
1 year ago

Calculations based on other 2 datasets

Hi,

I have 2 input tables shown below

CFY refers to Current FinYear and NFY refers to Next Finyear

Total Income

CompanyOctCFYNovCfyDecCFYJanNfyFebnfyMarnfyAprnfymaynfyjunnfyJulnfyAugnfySepnfyOctnfyNovnfyDecnfy
A101010101010101010101010101010
B101010101010101010101010101010
C101010101010101010101010101010
D101010101010101010101010101010
E101010101010101010101010101010
F101010101010101010101010101010

Admin Cost

CompanyOctCFYNovCfyDecCFYJanNfyFebnfyMarnfyAprnfymaynfyjunnfyJulnfyAugnfySepnfyOctnfyNovnfyDecnfy
A202020202020202020202020202020
B202020202020202020202020202020
C202020202020202020202020202020
D202020202020202020202020202020
E202020202020202020202020202020
F202020202020202020202020202020

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

 

Output Table

VAT

CompanyJancfyFebcfyMarcfyAprcfymaycfyjuncfyJulcfyAugcfySepcfyOctcfyNovcfyDeccfyJannfyFebnfyMarnfyAprnfymaynfyjunnfyJulnfyAugnfySepnfyOctnfyNovnfyDecnfy
A022.50022.50022.50022.510022.50022.50022.50022.50
E022.50022.50022.50022.510022.50022.50022.50022.50
D022.50022.50022.50022.510022.50022.50022.50022.50

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. 

Need Help
Tips and Tricks

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      GokilaRaviraj
      Helper II
      1 year ago

      Hi Anonymous 

       

      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 ? 
      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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