YTD values showing Blanks for missing ID

[AshDil]

Hi,

I have two tables

Table-1:

ID	DATE	SALES
1001	1/1/2022	2000
1001	2/1/2022	3200
1001	4/1/2022	2220
1001	6/1/2022	1500
1002	2/1/2022	4000
1002	3/1/2022	3000
1002	4/1/2022	900

Table -2:

ID	DATE	TARGET
1001	1/1/2022	1000
1001	2/1/2022	3000
1001	3/1/2022	2000
1001	4/1/2022	1500
1001	5/1/2022	5000
1001	6/1/2022	2000
1002	1/1/2022	3400
1002	2/1/2022	2300
1002	3/1/2022	4000
1002	4/1/2022	3000
1002	5/1/2022	2000
1002	6/1/2022	5000

Year and Month slicers are present for Visual.

In Slicers Year = 2022 and Month = 3 are selected. I wanted to show for ID = 1001, "YTD Sales Vs YTD Targets" = 5200/6000 = 86.7%

I tried to do it with following calculation

YTD SALES VS YTD TARGET =

VAR _Target = CALCULATE(YTD TARGET,TARGET[ID] IN VALUES(SALES[ID]))

RETURN

DIVIDE([YTDSALES],_Target)

But getting blank for ID = 1001 with above calculation as it is missing in March.

Please help me to do it.

Thanks,

AshDil

[amitchandak · Accepted solution]

@AshDil , Create a common ID and Date table

ID = distinct(Union(Distinct(Table1[ID]), Distinct(Table2[ID]) ) )

Create a date table using a calendar and the use ID from the ID table and Date from the date table filter

example of YTD
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[v-chenwuz-msft · Accepted solution]

Hi @AshDil ,

First, you need create Date Table which has no relationship with Fact table1 and table2. Then create a dim table of ID via 

ID = DISTINCT( UNION(VALUES('Table-1'[ID]),VALUES('Table-2'[ID])))
Or import a table. And create relationship between fact table1 and 2 with this ID table as the following screenshot.
 

The fields of the slicer year and month both comes from Date table.

At last, the calculate measure like this:

Measure =
VAR _date =
    EOMONTH ( MAX ( 'Table'[Date] ), 0 )
VAR _sale =
    CALCULATE (
        SUM ( 'Table-1'[SALES] ),
        DATESBETWEEN ( 'Table-1'[DATE], DATE ( YEAR ( _date ), 1, 1 ), _date )
    ) + 0
VAR _target =
    CALCULATE (
        SUM ( 'Table-2'[TARGET] ),
        DATESBETWEEN ( 'Table-2'[DATE], DATE ( YEAR ( _date ), 1, 1 ), _date )
    )
RETURN
    DIVIDE ( _sale, _target )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[amitchandak · Accepted solution]

@AshDil , Create a common ID and Date table

ID = distinct(Union(Distinct(Table1[ID]), Distinct(Table2[ID]) ) )

Create a date table using a calendar and the use ID from the ID table and Date from the date table filter

example of YTD
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[amitchandak]

@AshDil , Create a common ID and Date table

ID = distinct(Union(Distinct(Table1[ID]), Distinct(Table2[ID]) ) )

Create a date table using a calendar and the use ID from the ID table and Date from the date table filter

example of YTD
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))