Forum Discussion

Gururajv007's avatar
Gururajv007
Helper I
6 years ago
Solved

Output needed as per Criteria

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.

 

 

 

 

 

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

6 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Gururajv007 

     

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

     

    Best Regards

    Allan

    • v-alq-msft's avatar
      v-alq-msft
      Community Support
      6 years ago

      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's avatar
    v-alq-msft
    Community Support
    6 years ago

    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