fiscal month task

[ghouse_peer]

Hello , I am new to power bi, kindly  tell me how to Create a Total Sales variance % for all the Fiscal Months of the year..

[v-xicai · Accepted solution]

Hi @ghouse_peer ,

You may create relationship between the table "Time" and Sales, then create measures like DAX below.

Current Month Sales = CALCULATE(SUM(Sales[Regular_sales])+ SUM(Sales[Markdown_sales]),DATESMTD('Time'[Date]))



Last Month Sales = CALCULATE(SUM(Sales[Regular_sales])+ SUM(Sales[Markdown_sales]),DATESMTD(dateadd('Time'[Date],-1,MONTH)))



Variance %= IF([Last Month Sales ]<>BLANK(), DIVIDE([Current Month Sales],[Last Month Sales], 0)-1,0)

Then you may choose table or Matrix to display the result, and format the  data type of 'Time'[Date] as "yyyy-mm".

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@ghouse_peer ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

You can create measure like this for this month vs last month and take the variance

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

[ghouse_peer]

Hello @amitchandak 

I have a table 'Sales' 

from table 'sales' i need to extract new coulumn defination from 2 columns i,e Total sales =[Regular_sales]+[Markdown_sales], these 2 columns are presnt in sales.

In another table 'Time' i have column month where it contains dates in the format mm/dd/yy.

now the task is i need to create  Total Sales variance % for all the Fiscal Months of the year.. Kindly help