Current data by month

[Bohdan_H]

I have a table in which data on the level of English is entered every few months. If you choose one person, there will be 2-3 entries for that person. It is necessary to write a formula so that when choosing any month, it shows the data of the last entry at that time. And also to be able to create a pie chart where data on the number of people at each level will be indicated and these data will change depending on the selected month

[Bohdan_H]

Hi)

This is not exactly what I need.
For example, the data was entered in July, and I choose the month of December. I need in this case to display the latest relevant data on all people. That is, their last levels that were included in the table. And it should be so not only for December, choosing any month should reflect the latest relevant data for that month, taking into account what was entered before it.

[v-xinruzhu-msft]

Hi @Bohdan_H 

Do you mean to show data for the last level of the selected month and the last level of each month less than that month?

Best Regards!

Yolo Zhu

[Bohdan_H]

Hi @v-xinruzhu-msft 

I want that when selecting any month in the filter, it shows me the data that was recorded last. That is, if we choose December, and for the person in July level 3 was recorded, and in September level 4, it will show level 4. If we choose August, it will show level 3 because it was the last one entered.

[v-xinruzhu-msft · Accepted solution]

Hi @Bohdan_H 

Try the following suggestion.

1.The date table have no relationship with data table

Then create a column in data table

Max_level = MAXX(FILTER('Table',[Name]=EARLIER('Table'[Name])&&EOMONTH([Date],0)=EOMONTH(EARLIER('Table'[Date]),0)),[Level]) 

Then create a new measure

Measure = CALCULATE(SUM('Table'[Level]),FILTER('Table',EOMONTH([Date],0)<=EOMONTH(MAX('Table 2'[Date]),0)&&[Level]=[Max_level]))

Output

Best Regards!

Yolo Zhu

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

[Bohdan_H]

@v-xinruzhu-msft 

Thank you very much!

And the last question

Is it possible to make it so that it does not display the sum of the levels in the chart, but the number of those levels? for example, 4 levels are 8, 1 level is 1, etc.?

[Bohdan_H]

And to display not all levels in the graphics. And the last levels are precisely by "Name", because unic Name is less than 16

[v-xinruzhu-msft]

Hi @Bohdan_H 

You can refer to the following example

Data table

1.Create a date table, and create 1:N relationship between two table.

Table 2 = CALENDAR(DATE(2022,1,1),DATE(2023,1,31))

Then create a nre column in data table

MaxDate = MAXX(FILTER('Table',[Name]=EARLIER('Table'[Name])&&YEAR([Date])=YEAR(EARLIER('Table'[Date]))&&MONTH([Date])=MONTH(EARLIER('Table'[Date]))),[Date])

then create a measure in table 

Level = CALCULATE(SUM('Table'[Level]),FILTER('Table',[MaxDate]=[Date]))

Put the measure to table visual and pie chart

Output

Best Regards!

Yolo Zhu

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