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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
erhan_79
Post Prodigy
Post Prodigy

Last Day calculating

Hi there ;

 

I need your support for below issue , i have table visual and month filter slicer as below .Every month ,  4 times in a month i am uploading as below infos date , material and quantities to my dataset .Imagine that below table was continued to next months too, just i wanted to not to miz your mind so i gave an example for only january month cycle.

 

İ want that , in which date today it is not important , i am checking the report ,forexample i want to filter for january ,  just when i press filter bottom for january , system will show on the below table only the infos which are belong latest week of january  .It wıll work for february same too , and for next months and etc.

 

for example for below sample , today , i choosed the january month , table will show only the latest week şnfos of january ( the infos of 23.01.2021 ) , imagine that there would be info for january only for two sifferent week date , if i would ask februaryu filter system should show methe last week info also. ,  how can i manage this dynamic asking situation , could you give some advices pls 

 

Thanks in advance 

 

Capture.JPG

 

 

1 ACCEPTED SOLUTION

Hi @erhan_79 

I update my Slicer Table , Fact Table and measure.

New Fact Table:

Add WeekNum and YearMonth column.

WeekNum = WEEKNUM('Table'[Date],2)
YearMonth = YEAR('Table'[Date])*100+MONTH('Table'[Date])

1.png

Slicer Table:

Add a YearMonth column than before.

2.png

Measure:

Measure = 
VAR _SelectYearMonth = VALUES('YearMonthFilter'[YearMonth])
VAR _LastWeek = MAXX(FILTER(ALL('Table'),'Table'[YearMonth]=MAX('Table'[YearMonth])&&'Table'[YearMonth]IN _SelectYearMonth),'Table'[WeekNum])
Return
IF(ISFILTERED(YearMonthFilter[MonthName]),IF(MAX('Table'[YearMonth]) IN _SelectYearMonth&&MAX('Table'[WeekNum]) = _LastWeek,1,0))

Result is as below.

By default it will show blank.

3.png

Select 2021 January.

4.png

Select 2021 Jan and 2022 Feb:

5.png

 

Best Regards,

Rico Zhou

 

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

9 REPLIES 9
erhan_79
Post Prodigy
Post Prodigy

Dear @v-rzhou-msft  and Dear @Ashish_Mathur  ; 

 

i was on vacation so i saw your infos now , thanks you very musch firstly , i will check both situations /questions in aa few day and will reply this ticket as soon as possible 

Ashish_Mathur
Super User
Super User

Hi,

So when you select Jan, do you want to see the last 3 rows of Jan or do you want to see a single number 240?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-rzhou-msft
Community Support
Community Support

Hi @erhan_79 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

 

Best Regards,

Rico Zhou

v-rzhou-msft
Community Support
Community Support

Hi @erhan_79 

I think you may want to show values in the latest week in your select month. You can achieve your goal by measure.

My Sample Table:

Month and WeekNum are calculated columns.

 

Month = FORMAT('Table'[Date],"MMMM")
WeekNum = WEEKNUM('Table'[Date],2)

 

1.png

Filter Table:

2.png

Measure:

 

Measure = 
VAR _SelectMonth = SELECTEDVALUE(MonthFilter[MonthName])
VAR _MaxWeek = MAXX(FILTER(ALL('Table'),'Table'[Month]=_SelectMonth),'Table'[WeekNum])
Return
IF(MAX('Table'[WeekNum])=_MaxWeek,1,0)

 

Build a table visual to show Date,Material and Qty in Sample Table.

Then add this measure into filters on this visual and set it to show items when the value is 1.

Result is as below.

By default, it will show blank.

3.png

When I select Janurary in Slicer it will show values in 2020/01/23.

3.png

Best Regards,

Rico Zhou

 

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

Dear @v-rzhou-msft  ;

 

You clearly understood my problem and have been solved my question 

 

Thanks for your kind support , i have one more question too for your measure ;

i forgot to mention year changings , in my infos there would be more than diffrent years but same month names , i mean as you see in your measure example you solved the problem with the sample 23.01.2021 filtered date , but in the future in my table there would be infos with 23.01.2021 , 23.01.2022 , 23.01.2023 etc. So for this situtations system must give me the info of last year's sure. When we add year changins how can we manage these formula  ?

Hi @erhan_79 

From you statement,I think you want to show last year's Latest week values by slicer.

I update my sample and my measure.

Month,WeekNum and Year columns are all calculated columns.

Month = FORMAT('Table'[Date],"MMMM")
WeekNum = WEEKNUM('Table'[Date],2)
Year = YEAR('Table'[Date])

1.png

Measure:

Measure = 
VAR _SelectMonth = SELECTEDVALUE(MonthFilter[MonthName])
VAR _CurrentYear = MAXX(ALL('Table'),'Table'[Year])
VAR _LastYear = _CurrentYear-1
VAR _LastYearMaxWeek = MAXX(FILTER(ALL('Table'),'Table'[Year]=_LastYear&&'Table'[Month]=_SelectMonth),'Table'[WeekNum])
Return
IF(MAX('Table'[WeekNum])=_LastYearMaxWeek&&MAX('Table'[Year])=_LastYear,1,0)

Result is as below.

If I select January, it will show results in 2021/1/23.

2.png

Best Regards,

Rico Zhou

 

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

 

 

dear @v-rzhou-msft  ;

 

thansk for your reply ; we are very closer for the solution  🙂 now i will try to explain with more detail , can we manage like this ? 

 there will be 2 filter slicers , one of them year and the other one month name .  When i select year 2021 and month january system will show infos on the table visiual as your last example that you showed me ( the latest  week info of january 2021 ) 

 

If i will want to filter on year filter 2022 and on month filter February for example , then system will show me in the visiual table  the datas of the latest week of 2022 February .

 

and also i would like to see multiple filtering too  but i dont know how can we manage it , i mean at the same time  i would like to see multiple filtered  months , in one visual i would like to see latest week infos of 2021 january  , and latest week infos of 2022 february 

 

Hi @erhan_79 

I update my Slicer Table , Fact Table and measure.

New Fact Table:

Add WeekNum and YearMonth column.

WeekNum = WEEKNUM('Table'[Date],2)
YearMonth = YEAR('Table'[Date])*100+MONTH('Table'[Date])

1.png

Slicer Table:

Add a YearMonth column than before.

2.png

Measure:

Measure = 
VAR _SelectYearMonth = VALUES('YearMonthFilter'[YearMonth])
VAR _LastWeek = MAXX(FILTER(ALL('Table'),'Table'[YearMonth]=MAX('Table'[YearMonth])&&'Table'[YearMonth]IN _SelectYearMonth),'Table'[WeekNum])
Return
IF(ISFILTERED(YearMonthFilter[MonthName]),IF(MAX('Table'[YearMonth]) IN _SelectYearMonth&&MAX('Table'[WeekNum]) = _LastWeek,1,0))

Result is as below.

By default it will show blank.

3.png

Select 2021 January.

4.png

Select 2021 Jan and 2022 Feb:

5.png

 

Best Regards,

Rico Zhou

 

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

amitchandak
Super User
Super User

@erhan_79 , Expected output is not clear.

Try a measure

calculate(sum(Table[Qty]), filter(allselected(Table), Table[Date] =max(Table[Date]) && Table[material] =max(Table[material])))

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.