Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Inception to Date financials

Hi,   I have certain measures stored in the database table that are all inception to date financials at the quarter end. To represent these financials properly, I need to pick the amounts from the ...
Need Help
Show and Tell
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Ashish_Mathur ,

 

Thank you so much, the values match correctly.

 

I just had another follow-up question on the report layout. Other than, Debit and Debit_Sub, I have other measures as well as part of my report, but those are not stored Inception to Date, so they match correctly when Power BI sums up by default.

 

I had used Unpivot operation while building my dataset as earlier the measures were separate columns. 

Example - Debit, Debit_Sub, Measure 1, Measure 2 etc, were all separate columns in the table visualization. Due to the unpivoting, we see the column as Type and Amount with their details.

 

Since we have specifically used a separate calculation measure for Debit and Debit_Sub, how can we represent it under Type and Amount in the table visualization.

 

Should I rever the Unpivot back to the way it was done originally i.e. continue to have the measures shown as separate columns in the table visualization? Is there an alternate way to represent the data in the unpivoted form currently.

 

Thanks,

Vishy

 

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
6 years ago

You are welcome. The unpivoted data form in the best form to maintain data in.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Ashish, By unpivoted, you mean storing the measures in separate columns in the dataset right?

     

    Thanks,

    Vishy

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      6 years ago

      Hi,

      Unpivoting has nothing to do with how "measures are stored".  The way the source data is arranged right now is fine.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Ashish, Right now the way the dataset is , if I bring in the Type and Amount into my table visualization, it will by default sum up all the amounts. 

         

        Since we created specific measures catering to the financials that are stored As-At or Inception to Date like Debit and Debit_Sub, (which are part of Type alongwith other measures), it will not show the correct value in the table since our measures created for Debit and Debi_Sub are separate and not part of Type.

         

        My question is - In the current setup, since we have multiple measures stored under Type, some of which are Inception to Date and rest are not Inception to Date and can be summed up, I was thinking the best way would be to leave the measures as separate columns in the dataset and bring them into the table accordingly. Not sure if there is any other solution for this. 

         

        Thanks,

        Vishy