Push Dataset in powerBI

0
votes

I have a requirement where data from Event Hub is continuously coming to Stream Analytics and I am pushing it to POWERBI with a tumbling window of 15 mins.The Push data set will have data for a week.

The data is Transaction data where I have Amount and Transaction Date

For my requirement i need 3 charts

  1. total Amount for today till current time ..SO suppose 04th Nov (6:41 pm) it is $100

  2. change in Amount (Total Amount last week same day till same time- total Amount for today till current time) --Suppose last week it is 28 Oct till 6:41 pm it was @$80 so it should show @$20

  3. KPI where it find percentage difference between first two. 20*100/100=20%

    as I am using the tumbling window of 15 mins so i wont have transaction exactly till 6:41 but at least Amount where Max(Transaction Date)<= CurrentTime (6:41) for that day .

I am not sure how can I achieve 2nd and 3rd?

1
powerbi
For #2, if I understand correctly you can use the NOW() function and compare it to the datetime column to get today's and 7 days ago up until current time. Let me know if this makes sense, I can create a better answer if you show how the columns for datetime look. I do not understand #3 – SRT HellKitty
thanks SRT,but i tried using Now(),Today() and compare ,but somehow my filter for daterange is not working.I tried to use to "IF" just to compare and it works but filter are not working.ex:CALCULATE(SUM(Table.AMOUNT),filter(table,table.transactionDate=today()).This gives me blank data and if i do(IF(table.transactionDate=today(),"true","false")this shows values as I want. – user185981

1 Answers

0
votes

today = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[date]=TODAY()&&'Table'[datetime]<=NOW()))

total_last week = var lastweek=TODAY()-7 return CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[date]=lastweek&&'Table'[datetime]<=NOW()-7))