Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Filter table on values not empty depending on date

Hi there,

I have a table looking like this:

GTINDateAmountRevenue
400423148155917.04.202420499,20
400433878204726.04.2024214,20
400462560200304.04.20241192,47
400476400796707.05.202460101,95
400476400796730.04.20245085,00
400476400796702.04.20242034,00
400476400796726.04.20242034,00
400476400796717.04.20241017,00
400476400797407.05.20241016,99
400476400798130.04.2024100170,00
400476400798126.04.20242034,00
400476400798107.05.20241016,99

 

I have built a visual where the GTINs are in a pivotv showing the revenue depending on the months of the dates. 

But I would like to show only those entries, which have values in both months. So the end result should be looking like this:

GTINAprilMay
4004764007967170101,95
400476400798120416,99

 

Following measures have I tried, but did not worked yet:

CALCULATE(COUNTROWS(Sheet1), FILTER(Sheet1, NOT(ISBLANK(Sheet1[Revenue])))) 
--> this returns always a value where I cannot filter on it
 
Then I have tried to link the date column to a table date and did follwing:
Measure = CALCULATE(SUM(Sheet1[Revenue]), DATEADD(Sheet1[Date], -1, MONTH))
and 
If (ISBLANK([Measure]), 1, 0)
 
But this one delivers works only for those entries which have one entry in one month. When both months have values, it returns error.
 
Maybe it is simple but still I cannot see where the fault ist. Would be very appreciated if someone could help me here.
Thanks!
Need Help

2 Replies

  • johnbasha33's avatar
    johnbasha33
    Super User
    2 years ago

    Anonymous 

     

    1.Create a Measure for April Revenue

    April Revenue =
    CALCULATE(
    SUM(Sheet1[Revenue]),
    FILTER(
    ALL(Sheet1),
    MONTH(Sheet1[Date]) = 4
    )
    )


    2.Create a Measure for May Revenue

    May Revenue =
    CALCULATE(
    SUM(Sheet1[Revenue]),
    FILTER(
    ALL(Sheet1),
    MONTH(Sheet1[Date]) = 5
    )
    )


    3.Create a Measure to Check if Both Months Have Values*

    Both Months Have Values =
    IF(
    NOT(ISBLANK([April Revenue])) && NOT(ISBLANK([May Revenue])),
    1,
    0
    )


    Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      johnbasha33 I have tried this already, but the first two measures deliver the sum of revenue for all articles, not of each article. So that does not work for my issue.

      Any other ideas?