Forum Discussion

Karthik12's avatar
Karthik12
Icon for Post Patron rankPost Patron
3 years ago
Solved

DAX filter support for customized date

Hello Team,

Long time I am trying to get the solution for my requirement.

I would like to get a table with customized start date and end date.

Start date is 27.12.2022 and end date is always today minus 1 day (Start date is fixed and end date is dynamic like today minus 1 day)

 

 

 

 

Pbix file link :

https://vontier-my.sharepoint.com/:u:/g/personal/karthikeyan_palanisamy_gilbarco_com/EZvVsD0KXQZMvbeElHmdexUBQWLISsqCkbLCNhA7rrmLWA?e=YxfBK4

 

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Karthik12 ,

    You can create a calculated column as below to get the date range and apply the filter on the visual with the condition(Flag is not blank). Please find the details in the updated sample pbix file.

     

     

    Flag = 
IF (
    'dates'[Date] >= DATE ( 2022, 12, 27 )
        && 'dates'[Date]
            <= TODAY () - 1,
    'dates'[Date],
    BLANK ()
)

     

     

    Create a calculated column

    Apply visual level filter with the condition (Flag is not blank)

    Best Regards

6 Replies

  • ppm1's avatar
    ppm1
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Have you tried this table expression already?

     

    dates = CALENDAR(DATE(2022,12,27),TODAY()-1)
     
    Pat
     
    • Karthik12's avatar
      Karthik12
      Icon for Post Patron rankPost Patron
      3 years ago

      Hi ppm1 Thanks ! 

      I am new to DAX . started learning that one. also, Never tried before expression. I am bit diffcult to catch this expression like where to apply. Can you please apply in the pbix file and revert?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Karthik12 ,

        You can create a calculated column as below to get the date range and apply the filter on the visual with the condition(Flag is not blank). Please find the details in the updated sample pbix file.

         

         

        Flag = 
IF (
    'dates'[Date] >= DATE ( 2022, 12, 27 )
        && 'dates'[Date]
            <= TODAY () - 1,
    'dates'[Date],
    BLANK ()
)

         

         

        Create a calculated column

        Apply visual level filter with the condition (Flag is not blank)

        Best Regards

  • Karthik12's avatar
    Karthik12
    Icon for Post Patron rankPost Patron
    3 years ago

    Anonymous  Thanks you so much. It working fine.

    Small issue if we apply for a large number of data,  so many blanks in plan and actuals columns. Is there any measure to hide it?

    If Plan and Actual both Zero or blank, can we hide it?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Karthik12 ,

      Please update the formula of measure [Plan_sum] and [Actual] as below and check if it can return the correct result. Please find the details in the attachment.

      Plan_sum = 
VAR _sumofplan =
    SUM ( 'Plan sheet'[plan] )
RETURN
  IF ( ISBLANK ( _sumofplan ) || _sumofplan = 0, BLANK (), _sumofplan )
      Actual = 
VAR _sumofactual =
    SUM ( 'Production sheet'[Pro Qty] )
RETURN
    IF ( ISBLANK ( _sumofactual ) || _sumofactual = 0, BLANK (), _sumofactual )

      Best Regards

      • Karthik12's avatar
        Karthik12
        Icon for Post Patron rankPost Patron
        3 years ago

        Hi Anonymous Thanks. 

        Is it possible to replace Blank with 0 either plan or actual exists?