Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Bohdan_H
Frequent Visitor

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

photo_2023-01-31_18-53-27.jpg

1 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

vxinruzhumsft_0-1675331714809.png

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.

View solution in original post

7 REPLIES 7
Bohdan_H
Frequent Visitor

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.

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

 

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.

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

vxinruzhumsft_0-1675331714809.png

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.

@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.?

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

Hi @Bohdan_H 

You can refer to the following example

Data table

vxinruzhumsft_1-1675219126019.png

 

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

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

vxinruzhumsft_0-1675218962738.png

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

vxinruzhumsft_2-1675219149778.png

 

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.