Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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])
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.
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
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?
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 @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 @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])
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.
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])
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.
@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])))
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |