Rate of % Change

%3CLINGO-SUB%20id%3D%22lingo-sub-1422335%22%20slang%3D%22en-US%22%3ERate%20of%20%25%20Change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1422335%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys!%20Need%20your%20help.%20I'm%20struggling%20to%26nbsp%3B%20formulate%20the%20%25%20change%20in%20D%20A%20X.%26nbsp%3B%20I'm%20trying%20to%20have%20a%20measure%20for%20Daily%2C%20Week%20of%20the%20year%2C%20Monthly%2C%20Quarterly%20and%20yearly%20for%20comparative%20purposes.%20This%20is%20my%20data.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Relationships2.jpg%22%20style%3D%22width%3A%20772px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194908iC1B50BF7A839DBAB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Relationships2.jpg%22%20alt%3D%22Relationships2.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20first%20option%20is%20to%20get%20the%20previous%20value%20for%20daily%20to%20get%20the%20%25daily%20change.%20This%20is%20my%20formula%2C%26nbsp%3B%3C%2FP%3E%3CP%3Efor%3C%2FP%3E%3CP%3ESales%3D%20Sum(%5B%20%5D)%3C%2FP%3E%3CP%3Ethen%26nbsp%3B%3C%2FP%3E%3CP%3Efor%3C%2FP%3E%3CP%3EPrevious%20Sales%20%3D%20CALCULATE(%5BSales%5D%2CDATE%20ADD('Calendar'%5BDATE%5D%2C-1%2CDAY))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETake%20note%20of%20the%20fact%20date%20and%20Calendar%20date%2Cthey%20are%20different%20because%20%22Date%20add%22%20don't%20usually%20work%20in%20fact%20date%20or%20non%20continuous%20that's%20why%20I%20created%20Calendar%20table(continuous)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Untitled.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194910i832F3AB07FFFCF5E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Untitled.jpg%22%20alt%3D%22Untitled.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThis%20is%20the%20outlook.%20It%20did%20not%20met%20what%20I%20want.%20The%20Blank%20cells%20are%20not%20part%20of%20Fact%20Date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20Can%20I%20remove%20those%20blanks%3F%20or%20Should%20I%20change%20my%20formula%3F%20My%20data%20sets%3F%20Please%20need%20your%20help!!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1422335%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1423518%22%20slang%3D%22en-US%22%3ERe%3A%20Rate%20of%20%25%20Change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1423518%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668902%22%20target%3D%22_blank%22%3E%40beeaquino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDepends%20on%20goals.%20If%20there%20were%20no%20sales%20on%20previous%20date%20and%20blank%20is%20returned%2C%20that's%20correct.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20compare%20to%20the%20latest%20date%20before%20current%20when%20sales%20were%2C%20you%20may%20filter%20on%20max%20date%20in%20fact%20table%20before%20current%20date%20in%20calendar%20table.%3C%2FP%3E%0A%3CP%3EIf%20compare%20to%20the%20same%20date%20in%20previous%20month%20that%20will%20be%20another%20measure%2C%20etc.%20Period%20comparasion%20patterns%20are%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.daxpatterns.com%2Ftime-patterns%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.daxpatterns.com%2Ftime-patterns%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

Hi guys! Need your help. I'm struggling to  formulate the % change in D A X.  I'm trying to have a measure for Daily, Week of the year, Monthly, Quarterly and yearly for comparative purposes. This is my data. 

 

 

Relationships2.jpg

 

My first option is to get the previous value for daily to get the %daily change. This is my formula, 

for

Sales= Sum([ ])

then 

for

Previous Sales = CALCULATE([Sales],DATE ADD('Calendar'[DATE],-1,DAY))

 

Take note of the fact date and Calendar date,they are different because "Date add" don't usually work in fact date or non continuous that's why I created Calendar table(continuous)

 

Untitled.jpg

This is the outlook. It did not met what I want. The Blank cells are not part of Fact Date.

 

How Can I remove those blanks? or Should I change my formula? My data sets? Please need your help!!!

 

 

Thank you!

 

1 Reply

@beeaquino 

Depends on goals. If there were no sales on previous date and blank is returned, that's correct. 

If compare to the latest date before current when sales were, you may filter on max date in fact table before current date in calendar table.

If compare to the same date in previous month that will be another measure, etc. Period comparasion patterns are here https://www.daxpatterns.com/time-patterns/