Need to build a line and stacked column chart - struggling with it

[Anonymous]

Hey there,

I want to build a line and stacked column chart, its components will be

LINE VALUES:

- shows the cumulative number of PN (part numbers) at the end of every month (the table below shows values for August, for example)

- Details:

• When we see one PN number several times in a month (like PN 1282370 in August) - we count it as 1.
• We count every blank value in the PN column as a separate PN (like, we don't know whether they are the same or not)

SHARED AXIS:

- Would be just a name of the month

COLUMN SERIES:

- Values from the "Family" column

- Details:

• There are multiple families
• Some family values are blank, too

COLUMN VALUES:

- Count each "Family" type:

• If we take August values from the table below, the count would be: 
  • 2 for family S, 1 for blank. Why? Because we have to keep in mind the "Project Name" column:
    • So, for the month of August, Project "S 3" would have 3 PNs at risk
    • Project "H 6" would have 1 PN at risk
    • Project "300k" would have 3 PNs at risk
    • Total: 7 PNs at risk
    • The count of "Family" at the end of August would be 2: a count of 1 for family type S, and 1 value for the blank value.

Table for the chart:

help me, guys and gals, I beg you😭
@amitchandak 

 

[amitchandak]

@Anonymous , With help from date table you need measure like

Cumm Sales = CALCULATE(DistinctCount(Table[PN Number]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

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.

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.