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.
Dear Expert - Need help from you.
I would like to get the Output as per the date & Purpose from Table 1.
Criteria 1 : Calculation only for fiscal year Oct'2019 to Sep'2020 ( Current fiscal year )
Criteria 2 : [Actual data] should capture from Oct'19 to Jan20 ie Current month date.
Criteria 3 : If date is > current month, should capture [Plan].
Criteria 4 : If Data is not available in [Plan], then should capture [Forecast]
You can refer the Output needed table in the image.
Solved! Go to Solution.
Hi, @Gururajv007
I am sorry for the late reply. Based on your description, I created data to reproduce your scenario.
DateTable:
Table:
Here is the column and measure I created.
Rank = RANKX('DateTable',[Date].[Year]*100+[Date].[MonthNo],,ASC,Dense)
OutputValue =
IF (
MIN ( 'DateTable'[Date] )
< DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ),
CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Purpose] = "Actual" ),
IF (
CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Purpose] = "Plan" ) = BLANK(),
CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Purpose] = "Forecast" ),
CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Purpose] = "Plan" )
)
)
Finally, you may use the visual level filter to get the Month-Year you want to display.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Gururajv007
If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.
Best Regards
Allan
Thank you so much for your great help on time.
Need some expert advice here..Pl help.
Hi, @Gururajv007
I am sorry for the late reply. Based on your description, I created data to reproduce your scenario.
DateTable:
Table:
Here is the column and measure I created.
Rank = RANKX('DateTable',[Date].[Year]*100+[Date].[MonthNo],,ASC,Dense)
OutputValue =
IF (
MIN ( 'DateTable'[Date] )
< DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ),
CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Purpose] = "Actual" ),
IF (
CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Purpose] = "Plan" ) = BLANK(),
CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Purpose] = "Forecast" ),
CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Purpose] = "Plan" )
)
)
Finally, you may use the visual level filter to get the Month-Year you want to display.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Gururajv007
Could you please show me your sample data? Do mask sensitive data before uploading. Thanks.
Best Regards
Allan
Here is the raw data from Excel.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |