Calculating accurate sales data

[Anonymous]

Hi everyone, 

Happy Friday!

I have a table with a list of accounts. When someone selects an account datacards update to that particular account's information. 

If no data is selected on the table, I want the default display in the datacard to be "-" and not the first value. Can anyone help with this please? 

My second question is this: 

Company	Date	 Sales 
Fulham	01/01/2019	 52,402 
Fulham	02/01/2019	 20,521 
Fulham	03/01/2019	 54,548 
Fulham	04/01/2019	 40,041 
Fulham	05/01/2019	 51,340 
Fulham	06/01/2019	 48,950 
Fulham	07/01/2019	 35,430 
Fulham	08/01/2019	 26,909 
Fulham	09/01/2019	 47,437 
Fulham	10/01/2019	 34,887 
Fulham	11/01/2019	 51,005 
Fulham	12/01/2019	 36,178 
Fulham	01/01/2020	 22,112 
Fulham	01/03/2020	 40,004 
Fulham	01/06/2020	 39,168 

I want to calculate the YTD (2020) value of sales, but return "-" if nothing from the main table is selected or isblank. 

I want to take the start of the month from today's date and calculate last years equivalent 2019 sales (so from Jan to June of last year). Also returning "-" if nothing from the table is selected or isblank.

I have got completely stuck and would appreciate any help offered at all. 

Thank you

D

[V-pazhen-msft · Accepted solution]

@Anonymous 

1. For the YTD you could try this following formula, if you only need Year 2020, you can add another year filter to the dax.

YTD measure  = CALCULATE(SUM('Table'[Sales]),DATESYTD('Table'[Date]))

2. To get "-" when no selected you could create a measure. For example, this measure uses Isfiltered() to determine whether the company is filtered.

Measure = IF(ISFILTERED('Table'[Company]),[YTD measure],"-")

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Brilliant, thanks both! 

[parry2k]

@Anonymous you should HASONEFILTER function and check if a value is selected or not and based on that tweak your measure to return VALUE or '--'

As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡