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 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

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.

7 Replies

  • 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.

  • Bohdan_H's avatar
    Bohdan_H
    Frequent Visitor
    3 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        Bohdan_H
        Frequent Visitor
        3 years ago

        Hi Anonymous 

        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.