Inception to Date financials

[vishy86]

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

[Ashish_Mathur]

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.

[vishy86]

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 · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[vishy86]

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]

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

[vishy86]

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

Thanks,

Vishy

[Ashish_Mathur]

Hi,

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

[vishy86]

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

[Ashish_Mathur]

Hi,

I agree.  Keep seperate columns for each KPI.  We then write measures for each KPI depending upon the aggregation which we want to perform for each KPI - whether 'As at' or 'Sum'.

[vishy86]

Thank you @Ashish_Mathur.!!

[amitchandak]

@vishy86 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/

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Hard to follow exactly what you need. Sample data would help!