Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi , below is my data sample. I want to use DAX formula to calculate the data in desired column(either column or measure). Then I can use the new column or measure to draw a line chart.
Thanks in advanced
Proud to be a Super User!
Solved! Go to Solution.
Hi @ryan_mayu,
I made one sample for your reference. Both ways as below.
Measure:
1. Create a dimtime table using the formula and create the relationship between the date table and the fact table.
DIMTIME = CALENDARAUTO()
2. Create the measure as below.
Measure = VAR PRE = CALCULATE(SUM(Table1[Amount]),DATEADD(Table1[Date],-1,MONTH)) VAR CUR = CALCULATE(SUM(Table1[Amount])) RETURN IF(ISBLANK(PRE),0,(CUR-PRE)/PRE)
Calculated column:
Pre = CALCULATE(SUM(Table1[Amount]),DATEADD(Table1[Date],-1,MONTH))
result = IF(ISBLANK(Table1[Pre]),0,(Table1[Amount]-Table1[Pre])/Table1[Pre])
For more details, please check the pbix as attached.Regards,
Frank
Hi,
Please find attached.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi Ashish,
Thanks for your help. However, I am not sure why I can't access to the onedrive website. Could you please attach the pbix file in the reply?
Thanks
Ryan
Proud to be a Super User!
Hi @ryan_mayu,
I made one sample for your reference. Both ways as below.
Measure:
1. Create a dimtime table using the formula and create the relationship between the date table and the fact table.
DIMTIME = CALENDARAUTO()
2. Create the measure as below.
Measure = VAR PRE = CALCULATE(SUM(Table1[Amount]),DATEADD(Table1[Date],-1,MONTH)) VAR CUR = CALCULATE(SUM(Table1[Amount])) RETURN IF(ISBLANK(PRE),0,(CUR-PRE)/PRE)
Calculated column:
Pre = CALCULATE(SUM(Table1[Amount]),DATEADD(Table1[Date],-1,MONTH))
result = IF(ISBLANK(Table1[Pre]),0,(Table1[Amount]-Table1[Pre])/Table1[Pre])
For more details, please check the pbix as attached.Regards,
Frank
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |