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.
Hi Community,
Here, I have date columns in my table, and I have one FY Year Slicer also now i want to show
1. No.of months between End_MonthEnd and FY END (if End_MonthEnd is Blank then No.of months columns needs to show Zero else No.of months should come)
2. No.of months between Start_MonthEnd and FY END (if Year(Start_MonthEnd) = Slicer Slected FY Year then No.of months columns needs to show else No.of months columns needs to show Zero)
Start | Start_MonthEnd | End Date | End_MonthEnd | FY END |
6/12/2018 | 6/30/2018 | 3/31/2020 | ||
10/9/2018 | 10/31/2018 | 3/31/2020 | ||
10/11/2017 | 10/31/2017 | 3/31/2020 | ||
12/3/2018 | 12/31/2018 | 3/31/2020 | ||
9/18/2017 | 9/30/2017 | 3/31/2020 | ||
6/25/2018 | 6/30/2018 | 3/31/2020 | ||
2/5/2019 | 2/28/2019 | 4/22/2019 | 4/30/2019 | 3/31/2020 |
10/2/2017 | 10/31/2017 | 3/31/2020 | ||
12/5/2018 | 12/31/2018 | 3/31/2020 | ||
3/1/2010 | 3/31/2010 | 3/31/2020 | ||
4/22/2019 | 4/30/2019 | 3/31/2020 | ||
12/11/2018 | 12/31/2018 | 6/7/2019 | 6/30/2019 | 3/31/2020 |
9/10/2018 | 9/30/2018 | 3/31/2020 | ||
7/6/2015 | 7/31/2015 | 3/31/2020 | ||
2/1/2016 | 2/29/2016 | 3/31/2020 | ||
11/13/2017 | 11/30/2017 | 3/31/2020 | ||
5/14/2018 | 5/31/2018 | 5/24/2019 | 5/31/2019 | 3/31/2020 |
1/24/2019 | 1/31/2019 | 8/15/2019 | 8/31/2019 | 3/31/2020 |
6/18/2018 | 6/30/2018 | 6/4/2019 | 6/30/2019 | 3/31/2020 |
8/20/2018 | 8/31/2018 | 3/31/2020 | ||
12/1/2016 | 12/31/2016 | 3/31/2020 | ||
10/22/2018 | 10/31/2018 | 3/31/2020 |
I tried Datediff.. But i am not getting proper result
Please help me out from this
Thank you in advance
Regards
B V S S
Solved! Go to Solution.
Hi,
I assume that you want to Create this as a Columns. One thing is that You cant pass the Slicer value into the Table Column. A Slicer Value can be Accessed using SELECTEDVALUE() DAX function, but this can be used only with a Measure. If you want to show this as a part of the Table Visual rather than Table in Modelling, then you could use the below function. Please note that the Numbers indicate the answers to your questions.
Selected Month := VAR Selection_Year = SELECTEDVALUE(Table[Date].Year) RETURN <<Return Table Expression with Selection_Year Filter>>
1.
NOM_EndDate_FYEND:= IF(Table[End Date]<> BLANK(),DATEDIFF(Table[End Date], Table[FY END], Month),0)
2.
NOM_StartDate_FYEND:= IF(Table[End Date]<> BLANK(),DATEDIFF(Table[Start Date], Table[FY END], Month),0)
IF(Query1[End_MonthEnd]<> BLANK(),DATEDIFF(DATE(YEAR(Query1[End_MonthEnd]),MONTH(Query1[End_MonthEnd]),DAY(Query1[End_MonthEnd])),DATE(YEAR(Query1[FY END]),MONTH(Query1[FY END]),DAY(Query1[FY END])), Month),0)
Can you try with the above expression. Also, try to Resolve the Month(),Year(),Date() expressions as new columns to check if this is resolving properly if there are errors.
Best Regards,
Vignesh M
If what I suggested worked for you feel free to Drop a "Kudos" and Consider to "Accept as Solution" if I solved your Issue.
@Anonymous Please try this as "New Column"
No.of Months = VAR _Option1 = IF(Test302MonthsBtwn[End_MonthEnd]=BLANK(),0,DATEDIFF(DATEVALUE(Test302MonthsBtwn[End_MonthEnd]),Test302MonthsBtwn[FY END],MONTH)) RETURN _Option1
No.of Months 2 = VAR _Option2 = IF(YEAR(DATEVALUE(Test302MonthsBtwn[Start_MonthEnd]))=YEAR(Test302MonthsBtwn[FY END]),DATEDIFF(DATEVALUE(Test302MonthsBtwn[Start_MonthEnd]),Test302MonthsBtwn[FY END],MONTH),0) RETURN _Option2
Proud to be a PBI Community Champion
HI @Anonymous , @PattemManohar ,
Both of your solutions are working properly.. I made small mistake that's why previously it shows wrong values
Thank you for your help guys
Regards,
B V S S
@Anonymous Please try this as "New Column"
No.of Months = VAR _Option1 = IF(Test302MonthsBtwn[End_MonthEnd]=BLANK(),0,DATEDIFF(DATEVALUE(Test302MonthsBtwn[End_MonthEnd]),Test302MonthsBtwn[FY END],MONTH)) RETURN _Option1
No.of Months 2 = VAR _Option2 = IF(YEAR(DATEVALUE(Test302MonthsBtwn[Start_MonthEnd]))=YEAR(Test302MonthsBtwn[FY END]),DATEDIFF(DATEVALUE(Test302MonthsBtwn[Start_MonthEnd]),Test302MonthsBtwn[FY END],MONTH),0) RETURN _Option2
Proud to be a PBI Community Champion
Hi,
I assume that you want to Create this as a Columns. One thing is that You cant pass the Slicer value into the Table Column. A Slicer Value can be Accessed using SELECTEDVALUE() DAX function, but this can be used only with a Measure. If you want to show this as a part of the Table Visual rather than Table in Modelling, then you could use the below function. Please note that the Numbers indicate the answers to your questions.
Selected Month := VAR Selection_Year = SELECTEDVALUE(Table[Date].Year) RETURN <<Return Table Expression with Selection_Year Filter>>
1.
NOM_EndDate_FYEND:= IF(Table[End Date]<> BLANK(),DATEDIFF(Table[End Date], Table[FY END], Month),0)
2.
NOM_StartDate_FYEND:= IF(Table[End Date]<> BLANK(),DATEDIFF(Table[Start Date], Table[FY END], Month),0)
Hi @Anonymous,
Calculated column or measure anything is okay for me
Thank you in advance
Regards,
B V S S
Hi @Anonymous ,
Thank you for your quick response...
I tried with this
IF(Query1[End_MonthEnd]<> BLANK(),DATEDIFF(DATE(YEAR(Query1[End_MonthEnd]),MONTH(Query1[End_MonthEnd]),DAY(Query1[End_MonthEnd])),DATE(YEAR(Query1[FY END]),MONTH(Query1[FY END]),DAY(Query1[FY END])), Month),0)
Can you try with the above expression. Also, try to Resolve the Month(),Year(),Date() expressions as new columns to check if this is resolving properly if there are errors.
Best Regards,
Vignesh M
If what I suggested worked for you feel free to Drop a "Kudos" and Consider to "Accept as Solution" if I solved your Issue.
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |