Identified new entry and missing product from last month - Part 2

[Amardeep100115]

Hi 

i wish to see the results in which month which poduct is newly added and and stopped producing and it's count.

i have revised pivot and added one more column in data, in pivot you will find the red highlighted cells where some products are stop producing and in next month it's restarted to produce ( to identify the blank cell count please refer the column total values) and in last column i have put if formula to identify the difference. 

Now i wish to see the results in which month which poduct is newly added and and stopped producing and it's count 

Text column or unique Should be slicer/Dropdown filter

Visual should show currant month data if month is not selected in slicer and if we select any month then the data should be reflact acordingly.

Reference threat on community 

Sample data excel Files 

I am using below measure to get this 

hope i have expalained my results

Results whould be like below image:

If month is not selected then visual should reflact currant month data, see below error image:

Results after selecting months: 

[JirkaZ]

You can use ISFILTERED DAX to see if there is any value selected and then apply the appropriate calculation/measure.

[v-lili6-msft]

hi  @Amardeep100115 

If you may need to adjust the formula as below:

Measure 2 = IF(HASONEFILTER('Calendar'[Month Name]),[New this month],CALCULATE([New this month],FILTER('Calendar','Calendar'[Month Number]=MONTH(TODAY()))))

If not your case, what is your excepted output for "Visual should show currant month data if month is not selected in slicer and if we select any month then the data should be reflact acordingly."

Regards,

Lin

[Amardeep100115]

I got the answer by clicking the month by using your DAX measure but when i deselect the month visual shows (Blank), i have other visuals which shows me currant month data if month is not selected
Please suggest

[v-lili6-msft]

hi  @Amardeep100115 

In my measure, if you deselect the month, it will show current month data (Today is February), if you select February in the slicer, you could see that it is (Blank) in that month.

If is not your expected output, what expected output do you really want if no month selected in slicer?

Regards,

Lin

[Amardeep100115]

Hi @v-lili6-msft,

Is it possible to create new table in query editor or in tables with Month, Year, New and Closed count ?

Please help me in that way if possible

[Amardeep100115]

Hi,

in my dataset Feb data is not updated, and in place of "=Month(Today())" i have used "Currant month Name"i.e. "JAN". so it should be display the Jan data if there is no month selected in slicer and on selection of month data should be changed

See my file data snaps  before and after selection of month

[v-lili6-msft]

hi  @Amardeep100115 

Do you mean when you selected FY20, then Jan is the max month? if so, you'd better define FY month and FY yearmonth column in date table,

eg.

Month	FY Month No	FY YearMonth
oct	1
nov	2
dec	3
jan	4
feb	5
mar	6
apr	7
may	8
jun	9
jul	10
aug	11
sep	12

Then adjust the formula as below:

Measure 2 =

VAR _currentmonth=CALCULATE(MAX('Calendar'[FY YearMonth]),FILTER(Data,[New this month]<>BLANK())) return

IF(HASONEFILTER('Calendar'[Month Name]),[New this month],CALCULATE([New this month],FILTER('Calendar','Calendar'[FY YearMonth]= _currentmonth)))

Regards,

Lin

[Amardeep100115]

FY20 is a second slicer where i am using for changing the fiscal year, the data should be have MMM-YY wise NEW and CLOSED count in table

[Amardeep100115]

Hi @v-lili6-msft  and @JirkaZ,

Is it possible to create the table which has Month | Year | New count  | Churned count  in query editor or custom table?

Please suggest