Forum Discussion

bedot's avatar
bedot
Frequent Visitor
8 years ago

Subtraction between 2 column summarised differently

Hi all,

I'm simply looking for a difference between two columns in a table which has the figures summarised different - column A is set to "Don't Summarize" (Freq Number) and Column B is set to "Count" (Count Actual items). 

 

 

I'm hoping there's a simple DAX formula that can help! Let me know if there's figure information needed.

 

thanks

 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi bedot,

     

    If you can please share sampel data with expected result to help us clarify your requirement and coding formula.

     

    Regards,

    Xiaoxin Sheng

    • bedot's avatar
      bedot
      Frequent Visitor
      8 years ago

      Hi Xiaoxin,

       

       

      Would the above be enough detail? "Freq Number" = a look up based on today's date. E.g. Monthly today would be 7 now - where as "Count Actual Items" = a count of items we've actually recieved. The expected result is "Difference" highlighted in yellow, whcih clulates the difference between the two columns

       

      THanks

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi bedot,

         

        I'd like to know the calculate formula about freq number and the sample data about client.

         

        Regards,

        Xiaoxin Sheng

  • kohlivinayak's avatar
    kohlivinayak
    Resolver I
    8 years ago

    To what i understood you are calculating frequency starting from 1 jan, or 1 st of month

    and count from some table, i created this

     

     

     

     

     

     

     

     

     

    Created a measure of duration from this formula

     

    duration = SWITCH(MAX('Client freq'[Freq]),
        "Daily", DATEDIFF(DATE(YEAR(TODAY()),MONTH(TODAY()),01),TODAY(),DAY),
        "Monthly", DATEDIFF(DATE(YEAR(TODAY()),01,01),TODAY(),MONTH),
        "Quarterly", DATEDIFF(DATE(YEAR(TODAY()),01,01),TODAY(),QUARTER),
        "Half Yearly", ROUNDDOWN(DATEDIFF(DATE(YEAR(TODAY()),01,01),TODAY(),MONTH)/6,0),
        "Yearly", DATEDIFF(DATE(YEAR(TODAY()),01,01),TODAY(),YEAR)
        )

     

    and counting the occurance from below formula

     

    count = COUNT(Counter[Client]) +0

    added 0 to not have the field blank

     

    and normaly calculating the difference

     

    Diff = [duration] - Counter[count]

     

    Just make sure i am using measures for all this calculations.

    I don't know why you have yearly duration as 1 but if it is statis instead of the datediff you can pass 1 directly and it will work.