Dynamic Date Axis

[Anonymous]

I am creating data visualizations from the US COVID 19 tracking site, https://covidtracking.com/api/v1/states/daily.csv, specifically column charts. I am displaying four different data columns on the same chart using a slicer to pick the data type (see first DAX expression). On one chart, I'm using the reported date for the x-axis.

Period Value = IF(HASONEVALUE('Chart Selections'[Chart Type]),  SWITCH(VALUES('Chart Selections'[Chart Type]),

"Cases", AVERAGE(StatesHist[New Cases]), "Hospitalizations", AVERAGE(StatesHist[New Hosp.]),

"Deaths", AVERAGE(StatesHist[New Deaths]), "ICU", AVERAGE(StatesHist[New ICU])), "Please select only one value to display")

To minimize the day to day variation in results, I would like to group results on another chart in 1 to 14 day increments selected using a slicer with 14 rows indexed from 1 to 14. I tried using the DAX selection below which works in a Card visualization, but it is not supported in a calculated column. I tried using a measure instead, but measures are not supported as an axis. If I substitute SELECTEDVALUE(Data[Days],1) with a constant, the visualization works but is not dynamic. I can also substitute SELECTEDVALUE(Data[Days],1) with another measure that is just a constant and that works, but, again, it is not dynamic.

DatePeriod = CONVERT(INT(StatesHist[Date]/SELECTEDVALUE(Data[Days],1))*SELECTEDVALUE(Data[Days],1),DATETIME)

 I have a few questions:

1. Is there a way to get this to work with alternate DAX expressions?
2. Is there a way to dynamically update the bin value using a slicer (where bin value is a whole number between 1 and 14)?
3. Is there a way to update and Commit a constant in a measure?
4. Is there a way to dynamically update a DAX expression for a calculated column?

[v-xuding-msft]

Hi @Anonymous ,

I imported the csv file into Power BI Desktop. But I'm not clear about your description.  For the formula, you have at least 3 tables and the columns are not in this file. 

Can you please share a dummy pbix file or share a few screenshots to let us understand clearly?

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Xue,

It doesn't look like I can upload a file, but I can describe what I'm trying to do fairly simply. I have a data table with 'Data'[Days] where the column Days has 14 rows with the numbers 1 through 14. I have a slicer where I can pick from those 14 days. Right now, I'm using the constant '6' below, but I want to use the commented DAX expression instead. 

Bin Days = 6 //SELECTEDVALUE(Data[Days],1)

The Bin Days measure is used in the DAX expression for the following calculated column:

DatePeriod = CONVERT(INT(StatesHist[Date]/Data[Bin Days])*Data[Bin Days],DATETIME)

The DatePeriod column is used as the axis for the clustered column chart where the Period Value from my original post is the value on the chart. For simplicity, you can just use AVERAGE(StatesHist[New Cases]). In the screenshot, I also have the state OH selected.

The chart looks like this:

It's a very small file (365K) and not proprietary. Let me know if there is a way to post it somewhere (it's not obvious to me how you do that in this forum).

Thanks,

Tom Bonacuse