Subtraction between 2 column summarised differently

[bedot]

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

[kohlivinayak]

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.

[Anonymous]

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]

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]

Hi @bedot,

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

Regards,

Xiaoxin Sheng