Fiscal Year Total

[jhd]

Below is a "simple" version of my data table: 

Date	Month	Product	Sales_Value	Financial_Year	FY_Sales_Value
01/02/2018	Feb	A	500	2017	1100
02/02/2018	Feb	A	600	2017	1100
01/03/2017	Mar	A	400	2016	400
01/02/2018	Feb	B	700	2017	700

I calculate the column FY_Sales_Value

sumx(

filter(table1, Table1[Product]=earlier(Table1[Product]) &&

Table1[Financial_Year]=earlier(Table1[Financial_Year])), 

 Table1[Sales_Value])

The problem comes when I put this data into PowerPivot because I'm generally not interested in specific dates, just Months. The FY_Sales_Value then gets multiplied by the number of lines in the table per month.

I am therefore thinking that it is better to work out the FY_Sales_Value as a PowerPivot Measure, but I can't figure out how to do this. Hopefully someone can point me in the right direction.

Thanks.

[v-yulgu-msft]

Hi @jhd,

In Power BI desktop, after adding calculated column [FY_Sales_Value] into a table visual, to avoid it to be multiplied by the number of lines in the table per month, you can select "Don't summarize".

However, if you have any concern about using PowerPivot, I would suggest you post question on a more related forum.

Best regards,

Yuliana Gu