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.
Hello guys,
I am trying to create the YTG measure using month name and year. for e.g, if i select January and 2021 in filter, i am expecting the outcome from Feburary 2021 to December 2021.
I restricted to use the month name in filter insted of month number.
i will appreciate any suggestions.
Thanks
Solved! Go to Solution.
Hi @TaroGulati ,
Try like below :
step1, create silcer table :
Table2 = DISTINCT('Table'[YYYYMM])
MON = RIGHT(Table2[YYYYMM],2)
year = LEFT(Table2[YYYYMM],4)
Step 2,create month and year column on base table:
Month = FORMAT('Table'[Date],"MM")
Year = FORMAT('Table'[Date],"YYYY")
Then use the below measure :
NUMBER =
VAR MON =
CALCULATE (
MAX ( 'Table2'[MON] ),
FILTER ( ( 'Table2' ), MAX ( 'Table'[Month] ) > SELECTEDVALUE ( Table2[MON] ) )
)
VAR YEAR =
CALCULATE (
MAX ( 'Table2'[year] ),
FILTER ( ( 'Table2' ), MAX ( 'Table'[Year] ) = SELECTEDVALUE ( Table2[year] ) )
)
RETURN
IF ( MON <> BLANK () && YEAR <> BLANK (), MAX ( 'Table'[Numbers] ), BLANK () )
Get the below:
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Hi @TaroGulati ,
Try like below :
step1, create silcer table :
Table2 = DISTINCT('Table'[YYYYMM])
MON = RIGHT(Table2[YYYYMM],2)
year = LEFT(Table2[YYYYMM],4)
Step 2,create month and year column on base table:
Month = FORMAT('Table'[Date],"MM")
Year = FORMAT('Table'[Date],"YYYY")
Then use the below measure :
NUMBER =
VAR MON =
CALCULATE (
MAX ( 'Table2'[MON] ),
FILTER ( ( 'Table2' ), MAX ( 'Table'[Month] ) > SELECTEDVALUE ( Table2[MON] ) )
)
VAR YEAR =
CALCULATE (
MAX ( 'Table2'[year] ),
FILTER ( ( 'Table2' ), MAX ( 'Table'[Year] ) = SELECTEDVALUE ( Table2[year] ) )
)
RETURN
IF ( MON <> BLANK () && YEAR <> BLANK (), MAX ( 'Table'[Numbers] ), BLANK () )
Get the below:
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Hi @TaroGulati
To use the DATE code, convert the month name to number.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos✌️ !!
Hi,
Thank you for your response.
i am ristricted to use Month name in filter option. inside mesaure i need to mentioned selcted value of month name. do you know any function that can help to keep month name in filter but mesaure provide result based on month number?
Thanks
You can use your month name, so try to use/add this measure/code in your code to find the month number:
Measure =
var _M = SELECTEDVALUE('Sheet1'[Month])
return
month(DATEVALUE("2020/"&_M&"/01"))
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos✌️!!
Hi,
Thank you for the idea.
my half issue is resolved. only issue is that outcome is of month that i am selecting not YTG months. for e.g with below mesaures if i am selecting Januray i am getting value of Jan not from Feb to Dec.
i created one measure the convert the month name
i used above created mesaure in below measure:
I am not able to understand the issue.
Thanks
add 1 to the MONTH code like below to get Feb rather than Jan:
month(DATEVALUE("2020/"&_M&"/01"))+1
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos✌️!!
Hi,
i did add one month in below measure:
now below mentioned measure is not giving me any result
any idea where is still the issue?
Thanks
Hi @TaroGulati
Can you share your file here [after removing sensitive data]? And share a sample of your data in a table format here? And also, let me know the [total numbers] codes?
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos !!
Hi,
https://1drv.ms/x/s!AoMB6moPXeKTd9ZQHQ8-nsHGCFs?e=9TPCeZ
this is the link for the file that i am using.
measure for total number is this:
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 |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |