Output needed as per Criteria

[Gururajv007]

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.

[v-alq-msft]

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

[Gururajv007]

Thank you so much for your great help on time.

[Gururajv007]

Need some expert advice here..Pl help.

[v-alq-msft · Accepted 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.

[v-alq-msft]

Hi, @Gururajv007 

Could you please show me your sample data? Do mask sensitive data before uploading. Thanks.

Best Regards

Allan

[Gururajv007]

Here is the raw data from Excel.