Forum Discussion

amol0512's avatar
amol0512
Icon for Helper I rankHelper I
2 years ago

Show Last 3 months data in table

Hi,

I have this measure to show the last 3 months' values but it's showing only Dec-23, and Jan-24. There are 3 tables in model. 1-ErrorsLog, 2-Calendar, and another referenced 3-PreviousDate table with an inactive relationship with filter direction towards the main Calendar table.

The question is why it's not showing Feb-24 Data even though there is no filter applied. and if I add If(Result =Blank(), 0, Result) then it shows all previous months with actuals for Feb-24, Jan-24, Dec-23, and all previous months with 0.  

 

Total Errors = COUNTROWS('Error Log')
 
Total Erros 3 months =
VAR ReferenceDate =
    TODAY ()
VAR PreviousDates =
    DATESINPERIOD (
        'Previous Date'[Date],
        ReferenceDate,
        -3,
        MONTH
    )
VAR Result =
    CALCULATE (
        [Total Errors],
        REMOVEFILTERS ( 'Calendar' ),
        KEEPFILTERS ( PreviousDates ),
        USERELATIONSHIP ( 'Calendar'[Date], 'Previous Date'[Date] )
    )
RETURN
    Result
 

 


 

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi amol0512 ,

    If I understand correctly, the issue is that you couldn’t show last three months data in the table. Please try the following methods and check if they can solve your problem:

    1.Verify that the Error log table contains data for Feb-2024.

     

    2.Ensure that the relationships between Calendar, Previous Date, and Error log tables are properly configured.

     

    3.Modify the DAX formula to the following.

    Total Errors 3 months =
VAR ReferenceDate = TODAY()
VAR PreviousDates = DATEADD('Calendar'[Date], -3, MONTH)
VAR Result =
    CALCULATE (
        [Total Errors],
        REMOVEFILTERS ( 'Calendar' ),
        KEEPFILTERS ( PreviousDates ),
        USERELATIONSHIP ( 'Calendar'[Date], 'Previous Date'[Date] )
    )
RETURN IF ( ISBLANK( Result ), 0, Result )

     

    If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format to make a deep troubleshooting? It would be helpful to find out the solution.

    You can refer the following links to share the required info:

    How to provide sample data in the Power BI Forum

     

    Best Regards,

    Wisdom Wu

    • amol0512's avatar
      amol0512
      Icon for Helper I rankHelper I
      2 years ago

       

       

      I did If(Result= blank(), 0, Result) but it shows 0 value for previous months as well.

      I also suspect some filter propagation issues but not sure where and how. Refer the snap above. One to Many Calendar Date to Error Reported Date and one to many (inactive) PreviousDate Date to Calendar Date

  • amol0512's avatar
    amol0512
    Icon for Helper I rankHelper I
    2 years ago

    Do let me know if anybody has the answer to this. thank you