Fiscal year calculaion

[Anonymous]

Hi Guys,

        I am new to power bi. I want to compare sales with last month and last 3 months for the financial year say the year 2019 it  starts with April 2019 and end on March 2019. I used a Dax query as

Current month = CALCULATE(SUM(Table[Amount]),FILTER(ALLSELECTED(Date),Date[Month]=MAX(Date[Month])))

Last month = CALCULATE(SUM(Table[Amount]),FILTER(ALL(Date),Date[Month]=MAX(Date[Month])-1),

FILTER(ALL(Date),Date[Year]=MAX(Date[Year])))

Last 3 month = CALCULATE(SUM(Table[Amount]),FILTER(ALL(Date),Date[Month]=MAX(Date[Month])-2),

FILTER(ALL(Date),Date[Year]=MAX(Date[Year])))

The problem here is that if i chose April 2019 in the filter i want to get the April as current month 2019 value and there should be no value for the last and last 3 months because it becomes from last year 2018. But in my Dax Query The last month value is visible. Please Help me with a solution or an altenative Query.

 

[v-yuta-msft]

@Anonymous ,

Could you please share the sample data and clarify more details about this issue?

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft  Hi,

                               I am facing a problem in calculating the grand total of a column. 

i need to calculate the grand total in a column. I an having a measure displayed in the table format. measure = SUM(Table1[Sales Amount])/SUM(Table2[Qty]). the Sales amolunt is 920313461.42 the Qty is 3280190.

When i use this measure i am getting the correct values row wise but when you see the Grand total of the colum measure it is wrong.

It divides the sum of the total sales amount with the total sum of the qty and displays it as 280.6. But here i need to get the grand total of the column. How such a small no is displayed i am confused.  I need the grand total of the column. PLease help me with it