Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am new to power bi so please forgive me if its a simple silly solution.
I am working with data which has value for month-year for 2 years i.e. 24 columns, i want to creat a table/matrix wherein based on the users input of month and year all the columns till then would be displayed, for example if user input is Aug 23 all months from Jan 23 till Aug 23 should be displayed.
Solved! Go to Solution.
Month YTD =
VAR Months = ALLNOBLANKROW ( 'Table'[Month Name], 'Table'[Month] )
RETURN
GENERATE (
Months,
FILTER (
SELECTCOLUMNS ( Months, "@Month", 'Table'[Month] ),
[@Month] <= 'Table'[Month]
&& YEAR ( [@Month] ) = YEAR ( 'Table'[Month] )
)
)
thank you so much, it helped a lot.
Month YTD =
VAR Months = ALLNOBLANKROW ( 'Table'[Month Name], 'Table'[Month] )
RETURN
GENERATE (
Months,
FILTER (
SELECTCOLUMNS ( Months, "@Month", 'Table'[Month] ),
[@Month] <= 'Table'[Month]
&& YEAR ( [@Month] ) = YEAR ( 'Table'[Month] )
)
)
Hi @SM4 ,
Thanks for the reply from @tamerj1@VishalJhaveri , please allow me to provide another insight:
Thanks for your concern about this case.
I noticed you sent the data link but unfortunately I don't have permission to access it.
When I try to access the data, the page shows the following:
I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:
The first thing I did was to create a date table with two years of time using the dax function.
Here is the dax function:
Table 2 = CALENDAR("2023-1-1","2024-12-31")
I then duplicated this table and now have two identical data tables
Table = CALENDAR("2023-1-1","2024-12-31")
I am creating a slicer with the table's column field with month as the filter criteria
You can add filters by year and month to the slicer clock.
I then used table2 to display the final results of the filter, and created a measure for table2 called "M_".
here is the dax code for ”M_
M_ =
VAR _Slicer =
MAX ( 'Table'[Date] )
VAR _Slicermonth =
MONTH ( _Slicer )
RETURN
IF (
MAX ( 'Table 2'[Date] ) <= _Slicer
&& YEAR ( MAX ( 'Table 2'[Date] ) ) = YEAR ( _Slicer ),
MONTH ( MAX ( 'Table 2'[Date] ) ),
BLANK ()
)
The max function will filter out the maximum value of the current filter conditions
Now you can click on a month of a year in the filter to filter out data from January to the selected month of the year.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
+1
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
27 | |
24 | |
14 | |
9 |
User | Count |
---|---|
77 | |
61 | |
47 | |
17 | |
12 |