Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Running distinct count

I am a little confused on how to calculate a running distinct count, I have tried a bunch of ways with no luck, its driving me nuts. 

 

For context I have a Calendar table connected to another table (Table 1) on a one to many relationship (On dateid) then that table is connected to table 2 on a bi directional Many to 1. now from the calendar table I have a slicer on my dashboard called Fiscal Month. When I slice of Aug 2023 the following dax is not giving me a cumulative sum for some reason:

 

 

measure =

 

VAR CurrentFY = MAX('Calendar'[Fiscal Year])

RETURN

CALCULATE(
    DISTINCTCOUNT('table2'[CustomerID]),
    ALLEXCEPT('Calendar', 'Calendar'[Fiscal Year]),
    'Calendar'[Fiscal Year] = CurrentFY,
    DATESYTD('Calendar'[Fiscal Date], "6-30"),  --our FY starts in July 
    FILTER(
        'table 2',
        [Offers] > 0
    )
)
 
I am really lost on what I am doing wrong here. Thanks for the help!
Need Help
Show and Tell
Tips and Tricks

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Ashish_Mathur  heres an example file 

       

      now if you slice on the fiscal month you can see how the measure value is not doing a cumulatative sum. I am trying to make it sum up the total distinct IDs for the current FY. 

       

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        Hi,

        You should first create a unique list of all PSID's and create a Many to One relationship from Data and Data2 to this new list.  Thiw way you will be able to avoid the Many to Many.  Further, the other relationship was wrong which i have set right in the attached file.  Once you do what i have requested above, explain simple English your requirement so that i can help you with the measure.

        Hope this helps.