Forum Discussion

Bohdan_H's avatar
Bohdan_H
Frequent Visitor
3 years ago
Solved

Current data by month

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.