Forum Discussion
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
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])Slicer Table:
Add a YearMonth column than before.
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.
Select 2021 January.
Select 2021 Jan and 2022 Feb:
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.
9 Replies
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])))
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)Filter Table:
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.
When I select Janurary in Slicer it will show values in 2020/01/23.
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 Anonymous ;
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])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.
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.
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
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?
Dear Anonymous 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
