KPI Visual

[kabra_ashish]

I have a Data table and a fact table.

Date table - It has all the dates starting 01-Jan-2019 until 31-Dec 2020.

Custom created few more fields like Year, Month and "Month & Year".

Fact table - It has the dates (which is joined with the date table) and a column which has the amount spent.

A slicer is created using the "Month & Year" field from the date table.

I want to show the selected period (slicer) total spend as well as the total spend for the same period last year and the difference. So I chose KPI visual to showcase the same.

Current_Spend = Calculate(Sum('Table'[amount]))

The result pulled is only for the last day when using above formula. Example: When I select November 2020 it gives data for November 30 2020 only.

This problem was resolved by using ALL function as below:

Current_Spend = Calculate(Sum('Table'[amount]),All("Date_field")

However I am unable to pull off the Goal which should be complete November 2019 to do the comparison. It shows only last day of November 2019 i.e, 30-November-2019.

Any help here will be highly appreciated!

[v-janeyg-msft · Accepted solution]

Hi,@kabra_ashish 

According to your description, I tested it, I think you need to put the field of the slicer on KPI 'trend axis', for example, ‘month&year’.

Like this:

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[amitchandak]

@kabra_ashish , I think you should use time intelligence for that , with help from date table

example

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

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

With one date if should be able you give you

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

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 :radacad sqlbi My Video Series Appreciate your Kudos.