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 quarter month end i.e. Mar, Jun, Sep and Dec.

Example - 2020 Q1 amount should be picked up from month Mar 2020, 2020 Q2 will be picked up from month Jun 2020 and so on.

 

Right now, my financials are inflated as I have summed up the financials for each month of the quarter which is not right.

 

How can this be done within Power BI? 

 

My input is a SQL query.

 

Thanks,

Vishy

 

Need Help
Show and Tell
Tips and Tricks

12 Replies

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

    Anonymous You can have something like this with a Date calendar

    Measure =
var _max = maxx(allselected(Date[qtr-year]),Date[qtr-year])
var _maxDt = maxx(filter(Date,Date[qtr-year]=_max),Date[Date])
var _maxM = maxx(filter(Date,Date[Date]=_maxDt),Date[Month-year])

return
calculate(sum(Table[revenue]),filter(all(Date), Date[Month-year]=_maxM))

     

    But difficult say without a sample data and sample output.

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

  • 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