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.

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

Hi,

Assuming there is a Calendar Table with Year and Quarter columns, create a relationship from the Date column of your Data Table to the Date column of the Calendar Table.  To your visual/slicers, drag Year and Quarter from the Calendar Table and select a Year and Month.  Write these measures

Measure1 = SUM(Data[Amount])

Measure2 = CALCULATE([Measure1],DATESBETWEEN(Calendar[Date],EOMONTH(MAX(Calendar[Date]),-1)+1,MAX(Calendar[Date])))

Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi All,

     

    Managed to pull in some sample data in the link below.

     

    https://drive.google.com/file/d/1iZW8iOc0XPVyWp0N76Ktwnc7NHoLbI6n/view?usp=sharing

     

    I have a table visualization with the given dimentions and amounts in the above dataset.

    Company Id, Contract Id, Code, Year, Date, State, Type and Amount. Year is a separate entity and not a year extraction from the Date hence ignore the Year for now.

     

    I have another column created in my dataset called Quater Month which is nothing but a Quarter and Month pull from the Date column. The Quarter Month is a report level filter.

     

    The amount Debit and Debit_Sub is stored Inception to Date. When I bring in Type and Amount into my table visualization, by default, Power BI sums up which infaltes the amount and is not correct.

     

    So, if a user selects the Quarter Month as 2020Q1, then the Debit and Debit_Sub should just pull in the value as of quarter month end which is Mar 2020. Right now, it is summing up the amount for Jan 2020, Feb 2020 and Mar 2020 which is not correct.

    Same concept for any quarter month selected. 

     

    Hope this clarified the need. Please let me know if there are more questions.

     

    Thanks,

    Vishy

     

     

     

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      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