Calculate number of filled-in fields of latest record per month (for n-month period) needed

[JGRA]

Hi all,
I'm a newbe on the forum. I hope my question is at the right place and you're able to help me.

My issue: I've a database containing a table with productrecords. If a product is added, a new record is created with a number of fields, among with a statusfield 'A' and a timestamp field. After making a change to one of the fields, the current record is changed to status 'U' and a new record is created with status 'A', containing the actual (new) data. The 'U' status records are historical records.
To see the actual records, I can select all 'A' status records or just all distinct productrecords with the newest timestamp.
I want to have a report on the filled-status of some of the fields in this table per month over the last 12 month. There can be multiple updates in a month on a record. I've to have the latest value for each productrecord in a month to count. If in a particular month no changes are made, the latest records in the month before are valid and need to be counted.

My question: how can I create a report table (as below) to have a count on the filled status of fields at the end of each month over the last n-month?

Example table:

Id	product	timestamp	Fieldvalue1	Fieldvalue2	Status
1	Prod-1	12-03-2018 23:59			U
3	Prod-2	02-04-2018 08:32	Value 1		U
5	Prod-3	15-04-2018 09:02			U
2	Prod-1	25-04-2018 13:00	Value 2		U
4	Prod-2	26-05-2018 09:15	Value 3	Value 4	U
6	Prod-3	26-05-2018 10:00	Value 5		A
8	Prod-1	26-05-2018 14:15	Value 2	Value 6	U
10	Prod-2	27-05-2018 11:11	Value 3	Value 4	A
12	Prod-1	01-06-2018 10:00	Value 2	Value 7	A

Results in report:

Report:
	month-3	month-2	month-1	current month
	(march)	(april)	(may)	(june)
Total products	1	3	3	3
Fieldvalue1	0	2	3	3
Fieldvalue2	0	0	3	3

I hope I made myself clear about the issue and my expectations. Otherwise, let me know...

Thanks for your help.

Kind regards,

Johan

[v-yuta-msft · Accepted solution]

Hi JGRA,

To achieve your requirement, create three measures using DAX formula like this:

Total products = CALCULATE(DISTINCTCOUNT(Table1[product]), ALLEXCEPT(Table1, Table1[Month]))

Count of Fieldvalue1 = CALCULATE(DISTINCTCOUNT(Table1[Fieldvalue1]), ALLEXCEPT(Table1, Table1[Month]), Table1[Fieldvalue1] <> BLANK())

Count of Fieldvalue2 = CALCULATE(DISTINCTCOUNT(Table1[Fieldvalue2]), ALLEXCEPT(Table1, Table1[Month]), Table1[Fieldvalue2] <> BLANK())

PBIX: https://www.dropbox.com/s/cktiqmrx4cjx1p7/Calculate%20number%20of%20filled-in%20fields%20of%20latest...

Regards,

Jimmy Tao

[JGRA]

Thnkx for your response Jimmy. I will try this out.

Kind regards,

Johan