Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
i have a data base like one with columns: with date (by day), a column with hour (hour by hour) and other column with sales values
and i want to obtain in a table like this:
date hour sales sales0 (only for 15 may 2018)????how
20 may 2018 10 100 100
20 may 2018 11 70 70
20 may 2018 12 10 30
20 may 2018 13 50 5
i made a measure like :
Sales 15may = CALCULATE(average(table[sales]),table[date]="15 may 2018")
if i select on date filter other date than 15 may 2018 the results are blank
can anyone to help me please with the right formula? i'm stucked in this
Thanks!
Cosmin
Solved! Go to Solution.
Hi,
I hope i understood correctly your question.
Below is the formula i have used to calculate sales for one specific date (in my case it is Revenue for 1January2000):
Sales 1Jan2000 = CALCULATE(
Sum(Sales[Revenue]),
FORMAT('Date'[Date], "MM/DD/YYYY") = "01/01/2000"
)
The table is showing now Sales for Date 1January2000 regardless of the Date filter and whenever the filter is changed the Sales for 1st Jan remain the same. A variance can be calculated after that.
Regards,
Ioana
Hi,
I hope i understood correctly your question.
Below is the formula i have used to calculate sales for one specific date (in my case it is Revenue for 1January2000):
Sales 1Jan2000 = CALCULATE(
Sum(Sales[Revenue]),
FORMAT('Date'[Date], "MM/DD/YYYY") = "01/01/2000"
)
The table is showing now Sales for Date 1January2000 regardless of the Date filter and whenever the filter is changed the Sales for 1st Jan remain the same. A variance can be calculated after that.
Regards,
Ioana
Hi again
i have another issue regarding this topic
2 data set conected between them and a slicer with data from the first one; on the second when i put your measure, in the syntax doesn't work if i put dimension ignored to be the dimension which i use in that slicer
how can i do o correct sync and measure?
Thanks!
Cosmin
ok your measure
the variance not work, is it because i have to make these two measures as columns?
thanks
Hi cosminc,
Am I right in saying you are trying to compare the hours of a certain day (May 15th) with another?
I don't think you need a calculated column, the following two measures worked for me.
1) Measure = CALCULATE(sum(YourTableName[sales]),filter(ALLEXCEPT(YourTableName,YourTableName[hour]),YourTableName[date]=DATE(2018,05,15)))
2) Variance = [Measure]-sum(Sheet1[sales])
thanks
i used the solution from Ioana
thanks a lot to all for help!
Hi @cosminc
I can give you something but I think that's not what you want.
Why do you need sales for the 15 may 2018 ? To make a difference between two dates ?
You can add graphs / card box to your file and choose wich one will filter other visuals or not
Hi
yes i want to make another measure with % variation, not diffence but that's the point
the example with date was for an easier understanding, in reality my base has other column - i just want to add a column which contains data for a specific value form a column; and when i choose other value in filter i want that specific column to stay with fixed data and to compare with the column with the data for the value selected
can you give me a formula?
thnaks a lot!
Cosmin
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |