Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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.
Hi @bedot,
If you can please share sampel data with expected result to help us clarify your requirement and coding formula.
Regards,
Xiaoxin Sheng
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
Hi @bedot,
I'd like to know the calculate formula about freq number and the sample data about client.
Regards,
Xiaoxin Sheng